Sql-server – Must Declare Scalar Variable Error

sql serversql-server-2008-r2t-sql

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

Best Answer

@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.

Related Question