Passing parameters to SQL script using batch files

SQL Server 2005/2008 provides a command line utility SQLCMD, which can be used to perform basic SQL operations using command prompt. This comes really handy when automating repeatedly used SQL operations. You don’t need to Open the Management Studio every time to run a query or backup/restore a Database. All this can be scripted using SQLCMD in a batch file and run from the command prompt.

Need arises at times to pass the parameters to SQL scripts at runtime. This post described the way to achieve this.

In this example, we are discussing a particular scenario, where we need to connect to a remote SQL Server instance, read contents of a table in a database, and store the query results in a text file.

The following parameters will be passed at runtime:

  • SQL Server instance name

  • Database name

  • Table name

  • Output file location

We will first have a look at the sql file


use $(dbname)
select * from $(tablename)

It is a simple SQL file which selects the content of a table and displays it. The only unconventional thing you may notice is the use of $(dbname) and $(tablename) instead of the actual DBname and table name. This is because, these are variables, values for which will be passed while calling the sql script.

Now, lets have a look at the batch file which calls this SQL script.


sqlcmd -S %1 -i select.sql -v dbname = %2 tablename = %3 -o %4

We will call this batch file something like this:

CallSqlScript.cmd SqlServer01 EmployeeDb EmpContactTbl E:\SQLLogs\EmployeeContact.txt

Notice the 4 parameters we are passing to the batch file

This batch file is invoking the SQL Command line utility SQLCMD, the switch –S is used to specify the SQL Server name/instance which should be contacted. The value of this is set to %1 which means the first parameter passed to the batch file, SQLServer01 in our case.

-i switch specifies the input SQL file, which will be executed, select.sql in our case.

-v switch gives us an option of passing parameters to the SQL file select.sql.

We are passing 2 parameters to the SQL file, dbname and tablename, values of which are set to the 2nd and 3rd parameter being passed to the batch file respectively.

the last switch is the –o switch, which will save the output of the sql query to a plain text file. If we don’t use the –o switch, the output will be displayed on the screen.

Comments (19)

  1. Tom says:

    Your CallSqlScript.cmd uses a lower case s (i.e. -s) to specify the server name, but it should be an upper case -S.  I was getting errors until I fixed that.

  2. Thanks for pointing out Tom, I have corrected it.

  3. Tom says:

    Thank you for your post.  I got a lot out of it.

  4. Jel says:

    Very useful post. Thanks a lot

  5. Sneha says:

    Very informative. Thanks

  6. Vikram Kamath says:

    Thanks!…exactly what I was looking for….

  7. Prasad says:

    Thanks Sidarth for the valuable information…

  8. Jon says:

    This helped me out greatly. Thank you!! Only change I had to make was that I needed to quote the directory param.

    SqlCmd -U webuser -P webuser -S -i backup.sql -v dbname = %DBNAME% dirname = "%TEMP%"

    Contents of backup.sql

    BACKUP DATABASE TEST TO DISK='$(dirname)$(dbname).bak' WITH FORMAT


  9. Glad that it helped you 🙂

  10. Kumar says:

    Tks for the help…this is what I wanted….

  11. Jason Williams says:

    I used your example, but it appears that my code does  not work.  Looks like it's a file type issue, but the code errors with whether the file is ANSI or unicode.  Do you have any ideas?  Thanks, Jason

  12. Hi Jason,

    What is the exact error that you see?


  13. deadlydog says:

    Thanks, this is exactly what I needed!

  14. Ganesh says:

    I am trying to execute multiple sql files by keeping the isntance name input option but getting the errors as "f was unexpected at this time." any idea

    for %f in (*.sql) do


    sqlcmd.exe -S  %1 -E -d master -i "%f"


  15. Ganesh Pittala says:

    Please use beow to execute multiple tsql files on the same  location

    for %%f in (*.sql) do sqlcmd -S %serverName% -d master -i "%%f">>"%%f".log

  16. Nitin Matnani says:

    Exactly what I needed! Superb Bro!

  17. Akshay says:

    This is great post. but what about passing a string value to the same .SQL file to use it in where clause at runtime please?

  18. Ravinandan says:

    how can we pass ":" to .sql as a parameter? I tired but resulting in error .

  19. Caruncles says:

    I've been trying to do something like this. I want to date the file every day so it will have a unique name.  This would be run from a stored proc. is there any way this can be done using a date?

    declare @vardate varchar(8), @filename  varchar(25), @Now datetime

    SELECT  @Now = GETDATE()

    select @vardate = (Select CONVERT(varchar, DATEPART(mm,@Now)) +

     CONVERT(varchar, DATEPART(dd,@Now)) +

    CONVERT(varchar, DATEPART(yyyy,@Now)))

    select @filename = '"C:SFI_' + @vardate + '.csv"'

    –select @filename

    Execute xp_cmdshell 'SQLCMD -S VSQL2 -d MYdata -U me -P password -Q "Select * from tConsigneeOrder" -s "," -o @filename'