CODEWITHSUNDEEP
sqlsql-server
erik.ohlin
erik.ohlin

Reputation: 127

Pass Parameter To Dynamic SQL

I am trying to pass some parameters to a stored procedure that is called via dynamic SQL like so: 

CREATE PROCEDURE someProc
    @StartDate datetime,
    @EndDate datetime
AS
    DECLARE @qry varchar(max) = 'EXEC otherProc @StartDate, @EndDate'

    EXEC msdb.dbo.sp_send_dbmail 
         @recipients = '[email protected]',    
         @subject = 'Test',
         @query = @qry,
         @attach_query_result_as_file = 1,
         @query_attachment_filename = 'result.csv',
         @query_result_separator = '    ',
         @query_result_no_padding = 1,
         @query_result_width = 32767

But when I run this, I get an error

Must declare the scalar variable "@StartDate"

How can I change my query to get this to work?

Upvotes: 0

Views: 119

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Just write it in literally? Try it like this:

DECLARE @StartDate datetime='20170101';
DECLARE @EndDate datetime=GETDATE();


DECLARE @qry varchar(max) = 'EXEC otherProc {ts''' 
                           + REPLACE(CONVERT(VARCHAR(19),@StartDate,126),'T',' ')  
                           + '''}, {ts''' 
                           + REPLACE(CONVERT(VARCHAR(19),@EndDate,126),'T',' ')  + '''}';

SELECT @qry;

The result:

EXEC otherProc {ts'2017-01-01 00:00:00'}, {ts'2017-07-12 22:20:39'}

Upvotes: 1

Related Questions