3

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
0

3 Answers 3

2

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

1
  • 1
    Technically speaking, the @id variable inside @sql is in a different scope to the declare @id statement. Hence SQL Server displays the error about @id not being declared.
    – Hannah Vernon
    Mar 24, 2016 at 14:16
3

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
1
  • 1
    This is a useful tip and in terms of best practice is a better answer than mine, which merely fixed your code. sp_executesql is also more secure than working with literals from a SQL injection POV. From memory, it doesn't work with all parameters though. Table variables being one example. You can't pass them in. Horses for courses.
    – Molenpad
    Mar 24, 2016 at 20:10
1

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.