Sql-server – Pass parameters to a procedure with SQLcmd

parametersql serversqlcmdstored-procedures

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?

Best Answer

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
Related Question