3

I use this SQL command:

 sqlcmd -v first_date="30/09/2015" last_date="15/09/2016"

And I would like to pass that parameters to a stored procedure:

ALTER PROCEDURE [dbo].[_testProcedure]
    WITH EXECUTE AS CALLER
AS
BEGIN

    DECLARE @F_FIRST_DATE_DT VARCHAR(50)
    DECLARE @F_LAST_DATE_DT VARCHAR(50)

    SET @F_FIRST_DATE_DT = N'$(first_date)'
    SET @F_LAST_DATE_DT = N'$(last_date)'

But it return me an error:

Incorrect syntax near 'first_date'

What is the correct way to pass variables to stored procedure?

  • Your SQLCMD command line doesn't contain or reference any query to execute. It should have either a -q "your_query" / -Q "your_query or a -i "sql_file". What script specifically are you executing with SQLCMD? – Andriy M Jul 15 '15 at 5:47
2

I got these two examples to work from the command line:

sqlcmd -S .\sql2014 -E -Q "EXEC tempdb.[dbo].[_testProcedure] $(first_date), $(last_date)"  -v first_date = '30/09/2015' last_date = '31/12/2015'
GO

sqlcmd -S .\sql2014 -E -i "d:\temp\temp.sql"  -v first_date = '30/09/2015' last_date = '31/12/2015'

If you are using the -Q switch to pass in a query, just make the variables part of the SQL string. If you are calling a script, then the script should include the variables, eg in my example temp.sql contains the following SQL:

EXEC tempdb.[dbo].[_testProcedure] $(first_date), $(last_date)

HTH

2

There is no special way to pass SQLCMD variables to a Stored Procedure. Stored Procedures are encapsulated code and cannot be passed SQLCMD variables as you are attempting to do in the code posted in the Question.

Stored Procedures take input parameters to pass in values. And those input parameters should be of the correct datatype for the data being passed in; you do not need to accept VARCHAR for date values simply because SQLCMD variables do not specify their type.

CREATE PROCEDURE [dbo].[_testProcedure]
    WITH EXECUTE AS CALLER
(
  @FirstDate DATETIME,
  @LastDate DATETIME
)
AS
SET NOCOUNT ON;

SELECT DATEDIFF(DAY, @FirstDate, @LastDate) AS [NumberOfDays];

Then you call it in one of the following ways:

  • EXEC [dbo].[_testProcedure] first_date, last_date;
  • EXEC [dbo].[_testProcedure] @FirstDate = first_date, @LastDate = last_date;

Now, IF you are executing the above T-SQL from SQLCMD, then you can pass in SQLCMD variable into that T-SQL, which is the current query:

-Q "EXEC [dbo].[_testProcedure] $(first_date), $(last_date);"

or:

-Q "EXEC [dbo].[_testProcedure] @FirstDate = $(first_date), @LastDate = $(last_date);"

Also, you need to pass in SQLCMD variable values using single-quotes, not double quotes. Try the following to see why:

SQLCMD -Q "PRINT $(FirstDate);" -v FirstDate='12/05/2006'

Returns:

12/05/2006

But:

SQLCMD -Q "PRINT $(FirstDate);" -v FirstDate="12/05/2006"

Returns:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.