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
– Sean Lange, Commented Feb 5, 2020 at 19:38

David Browne - Microsoft · Accepted Answer · 2020-02-05 19:41:46Z

1

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;

edited Feb 5, 2020 at 19:41

answered Feb 5, 2020 at 19:38

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Lange Over a year ago

You will need a space after "FROM" also. But still this is vulnerable to sql injection but that is more of a design problem and can't be solved when passing in the table name.

Collectives™ on Stack Overflow

Dynamic SQL "Declare Scalar Variable" error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related