CODEWITHSUNDEEP
sql-serverstored-proceduresdynamic-sql
awilliams8976
awilliams8976

Reputation: 91

Dynamic SQL "Declare Scalar Variable" error

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.

Upvotes: 0

Views: 690

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

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;

Upvotes: 1

Related Questions