1

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
  • 3
    @molenpad72 answer is correct, but for further tutorial/help have a read on this article. Scenario 3 is what your error fell under sqlhints.com/2015/06/28/… – user2676140 Mar 24 '16 at 13:25
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
    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. – Max Vernon Mar 24 '16 at 14:16
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
  • 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 '16 at 20:10
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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