Must Declare Scalar Variable Error

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

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

StackExchange Database Administrators Q#133273, answer score: 3