Must Declare Scalar Variable Error

Question

My syntax keeps giving me the below error, which is blowing my mind as I think (and please kindly correct me if I am incorrect), I have declared and set this variable above.

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

Here is my syntax, and if I include a print @id statement the proper value will be output, however I still get the above error?!

Create Table #temphold
(
  dateadded datetime
  ,dateupdated datetime
  ,id varchar(100)
)

Declare @id varchar(100), @sql varchar(max)
Set @id = '12345'


set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(@id,'''') '
PRINT @SQL
EXEC(@SQL)


Drop Table #temphold

Answer 1

@id as part of the execution variable @sql is doing nothing. It is not tied to the declared and set variable unless you build the string around it, i.e. concatenate it to the string like this:

set @sql = 'insert into #temphold(dateadded,dateupdated,id) ' +'select getdate(),getdate(),COALESCE(' + @id + ','''') '

Notice the + either side of the @id variable.

At the end of the day, @sql is just a string until it's executed using the EXEC() command. Simply treat it as such until it compiles as fully qualified T-SQL.

Answer 2

You can also pass @id in as a parameter to the dynamic query instead of putting it in as a literal. To do this you need to use the sp_executesql function instead of EXEC. IMO passing parameters into the statement helps make the generated statement a little more clear/readable and you don't have to cast or convert certain data types into a nvarchar equivalent. Doing it this way also gives you a better chance of SQL Server generating a query plan that it could reuse.

Here is a post on Stack Overflow that provides some compare and contrasts. Stored procedure EXEC vs sp_executesql difference?

Here is Microsoft's technet article on sp_executesql

Create Table #temphold
(
  dateadded datetime
  ,dateupdated datetime
  ,id varchar(100)
)

Declare @id varchar(100), @sql nvarchar(max)
Set @id = '12345'


set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(@id,'''') '
PRINT @SQL;
execute sp_executesql @statement = @sql, @parameters = N'@id varchar(100)', @id = @id

Drop Table #temphold

Answer 3

The variable @sql only lives where it's defined. Definitions are delimited by a 'GO' statement. See if this works for you...

Set @id = '12345'
Set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(@id,'''') '
GO
PRINT @sql;
execute sp_executesql @statement = @sql, @parameters = N'@id varchar(100)', @id = @id

Drop Table #temphold