Dynamic SQL "Declare Scalar Variable" error

Question

I've created the following procedure and every time I try to execute it I get the error

Must declare the scalar variable @BatchId

Essentially, all that I'm trying to do is insert the contents of a raw table into a master table with a batch id (created by a sequencer) for all inserted rows. This seemed simple enough but isn't working properly.

CREATE PROCEDURE [dbo].[usp_SessionsAppend]
    @RawTable NVARCHAR(500)
AS 
    DECLARE @BatchId BIGINT, @SQLString NVARCHAR(MAX)

    SET @BatchId = NEXT VALUE FOR [dbo].[BatchID]

    SET @SQLString = 
        'INSERT INTO [Master].[Sessions] (
         [ImportTimestamp]
        ,[TransactionId]
        ,[ParticpantId]
        ,[ProviderId]
        ,[ActivityDate]
        ,[Attended]
        ,[Minutes]
        ,[SurveyCompleted]
        ,[Instructor]
        ,[InstructorID]
        ,[ProgramCode]
        ,[BatchId]
        )

        SELECT
         GETDATE() AS [ImportTimeStamp]
        ,NEWID() AS [TransactionId]
        ,[ParticpantId]
        ,[ProviderId]
        ,[ActivityDate]
        ,[Attended]
        ,[Minutes]
        ,[SurveyCompleted]
        ,[Instructor]
        ,[InstructorID]
        ,[ProgramCode]
        ,@BatchId
        FROM' + @RawTable

    EXECUTE (@SQLString)

Any help or insight would be greatly appreciated.

The reason for this error is the the variable @BatchId is out of scope in your dynamic sql. But that is only part of the issue. You also are vulnerable to sql injection here. Why do you have to pass in the table name? That is an indication of a design that is less than ideal. — Sean Lange

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2020-02-05T19:38:33

Use sp_executesql to pass parameters into the dynamic SQL.

eg

 declare @BatchId int = NEXT VALUE FOR [dbo].[BatchID]

 declare @RawTable nvarchar(200) = 'foo';

 declare @SQLString nvarchar(max) = 
        'INSERT INTO [Master].[Sessions] (
         [ImportTimestamp]
        ,[TransactionId]
        ,[ParticpantId]
        ,[ProviderId]
        ,[ActivityDate]
        ,[Attended]
        ,[Minutes]
        ,[SurveyCompleted]
        ,[Instructor]
        ,[InstructorID]
        ,[ProgramCode]
        ,[BatchId]
        )

        SELECT
         GETDATE() AS [ImportTimeStamp]
        ,NEWID() AS [TransactionId]
        ,[ParticpantId]
        ,[ProviderId]
        ,[ActivityDate]
        ,[Attended]
        ,[Minutes]
        ,[SurveyCompleted]
        ,[Instructor]
        ,[InstructorID]
        ,[ProgramCode]
        ,@BatchId
        FROM ' + quotename(@RawTable)

    print @SQLString
    exec sp_executesql @SQLString, N'@BatchId int', @BatchId = @BatchId;

Dynamic SQL "Declare Scalar Variable" error

1 Answers