Freigeben über

Passing parameters to SQL script using batch files

  • Artikel

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

select.sql

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.

CallSqlScript.cmd

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

  • Anonymous
    June 08, 2009
    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.

  • Anonymous
    June 08, 2009
    Thanks for pointing out Tom, I have corrected it.

  • Anonymous
    June 09, 2009
    Thank you for your post.  I got a lot out of it.

  • Anonymous
    June 15, 2009
    Very useful post. Thanks a lot

  • Anonymous
    November 18, 2010
    Very informative. Thanks

  • Anonymous
    January 06, 2011
    Thanks!...exactly what I was looking for....

  • Anonymous
    April 19, 2011
    Thanks Sidarth for the valuable information...

  • Anonymous
    July 15, 2011
    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 dev000660.us.dataflux.com -i backup.sql -v dbname = %DBNAME% dirname = "%TEMP%" Contents of backup.sql BACKUP DATABASE TEST TO DISK='$(dirname)$(dbname).bak' WITH FORMAT GO

  • Anonymous
    July 17, 2011
    Glad that it helped you :)

  • Anonymous
    September 29, 2011
    Tks for the help...this is what I wanted....

  • Anonymous
    April 20, 2012
    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

  • Anonymous
    April 22, 2012
    Hi Jason, What is the exact error that you see? -Sidharth

  • Anonymous
    May 31, 2012
    Thanks, this is exactly what I needed!

  • Anonymous
    June 06, 2012
    The comment has been removed

  • Anonymous
    August 24, 2012
    The comment has been removed

  • Anonymous
    January 23, 2013
    Exactly what I needed! Superb Bro!

  • Anonymous
    December 09, 2013
    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?

  • Anonymous
    October 30, 2014
    how can we pass ":" to .sql as a parameter? I tired but resulting in error .

  • Anonymous
    November 21, 2014
    The comment has been removed