CODEWITHSUNDEEP
sql-servert-sql
Scott and the Dev Team
Scott and the Dev Team

Reputation: 2431

Dynamic SQL won't execute

Here is the original SQL statement that runs with no problem:

INSERT  INTO @MasterTable ( Col1 , Col2 , Col3 , Col4 , Col5 ,  Col6, Col7, Col8, Col9  ) 
EXEC Triage.Micro.DeptChanges @Client,@GroupCode, 0, @Ref1, @Ref2, @Ref3

When I try to do the same thing with dynamic sql, it gets errors. I do not know how to fix it. Here is the code to generate and run the dynamic SQL:

DECLARE @strSQL NVARCHAR(MAX), @strColumn VARCHAR(MAX), @strProc VARCHAR( MAX)

SELECT  @strColumn = StoredProcName ,
        @strProc = SPROC
FROM    Triage..tblReports2FileName
WHERE   ReportCode = 'R005';

SET @strSQL = N'INSERT  INTO @MasterTable ' +  @strColumn + N' EXEC ' + @strProc

EXEC sp_executesql @strSQL

Here are the errors I received:

Msg 1087, Level 15, State 2, Line 26
Must declare the table variable "@MasterTable".

Msg 137, Level 15, State 2, Line 26
Must declare the scalar variable "@Client".

Upvotes: 0

Views: 323

Answers (1)

BeanFrog
BeanFrog

Reputation: 2315

Dynamic sql runs in it's own batch - so it will not recognise the variables declared in the script calling it.

You can pass through variables to dynamic sql as follows:

declare @parmdefinition nvarchar(500);
set @parmdefinition = N'@Client char(10), @GroupCode nvarchar(20)';
EXEC sp_executesql @strSQL, @parmdefinition, @Client=@Client, @GroupCode=@GroupCode;

As mentioned in Laeeq's answer - in order to populate the table, you will have to use a temporary table (#MasterTable) rather than a table variable.

If @MasterTable just holds the name of the table, then subsitute it into the string earlier as follows:

SET @strSQL = N'INSERT  INTO ' +  @MasterTable + ' ' +  @strColumn + N' EXEC ' + @strProc

Upvotes: 3

Related Questions