How to Pass in Parameters to a SQL Server Script Called with SQLcmd

How to pass in parameters to a SQL Server script called with sqlcmd?

Use the -v switch to pass in variables.

sqlcmd -v varMDF="C:\dev\SAMPLE.mdf" varLDF="C:\dev\SAMPLE_log.ldf"

Then in your script file

CREATE DATABASE [SAMPLE] ON  PRIMARY 
( NAME = N'SAMPLE', FILENAME = N'$(varMDF)' , SIZE = 23552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SAMPLE_log', FILENAME = N'$(varLDF)' , SIZE = 29504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

SQLCMD Passing in Variables

You can also use the -v option to set a scripting variable that exists in a script. In the following script (the file name is testscript.sql), ColumnName is a scripting variable.

USE AdventureWorks2012;
SELECT x.$(ColumnName)
FROM Person.Person x
WHERE c.BusinessEntityID < 5;

You can then specify the name of the column that you want returned by using the -v option:

sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql

To return a different column by using the same script, change the value of the ColumnName scripting variable.

sqlcmd -v ColumnName ="LastName" -i c:\testscript.sql

More documentation:
https://msdn.microsoft.com/en-us/library/ms188714.aspx

Passing parameters to stored procedure using a SQLCMD batch file

First you need to deploy (create) the SP in your target database. If c:\sp_TEST.sql contains the SQL of sp_TEST, you are just deploying it, not running it, and no parameters required to create the SP.

-v is to specify sqlcmd parameter, not SP parameter. To call the SP with the given parameter, you need to define a sqlcmd parameter in the sql to call the SP:

sqlcmd -S %SQLInstance% -d %DatabaseName% -l0 -U %BPOUserName% -P %BPOPassword%  -Q"EXEC sp_TEST @Username='$(UserName)'" -v UserName="%test1%"

Thanks @Jeroen's comments. It also works without using the sqlcmd variable:

sqlcmd -S %SQLInstance% -d %DatabaseName% -l0 -U %BPOUserName% -P %BPOPassword%  -Q"EXEC sp_TEST @Username='%test1%'"

Pass parameters to a procedure with SQLcmd

If you will declare @F_FIRST_DATE_DT and @F_LAST_DATE_DT as input parameters of your stored procedure - you will be able to call it like

EXEC @return_value = [dbo].[_testProcedure] 
                         @F_FIRST_DATE_DT = $(first_date), 
                         @F_LAST_DATE_DT = $(last_date)

in your script you're running with sqlcmd.

You're getting error because $() syntax is valid for accessing external varables only in sqlcmd script, and not in stored procedures.

SQL Script Command Line Arguments

OK.. If anyone cares, it Goes Like This...

The SQL Script file which is using the variable uses the variable like so:

Update TableName Set ColumnValue=$(Param)

--Notice there's no declare, and the format of the parameter uses

$(Param) instead of @Param as a variable...

The Command line looks almost the same. For some reason, the passed value needs to be in single quotes within double quotes, like so:

SQLCMD -S ServerName -d DatabaseName -v Param="'Param'" -i"ScriptFile.sql"

Use just double quotes around the ScriptFile name, Single within Double for the Parameters.

---

Related Topics