How to resolve the error - must declare the scalar variable @id?

4.1K Asked by AndreaBailey in SQL Server , Asked on Oct 4, 2022

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

Answered by Anil Jha

To resolve the error - must declare the scalar variable @id -

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

Your Answer

Answer (1)

Ranjana

The error "Must declare the scalar variable '@Id'" typically occurs in SQL Server when you're trying to execute a query or a stored procedure that references a variable @Id, but the variable hasn't been declared or assigned a value.

To resolve this error, you need to make sure that you've declared the @Id variable before using it in your SQL query or stored procedure, and that you've assigned it a value. Here's how you can declare and assign a value to @Id before using it:

DECLARE @Id INT; -- Declare the @Id variable

SET @Id = 123; -- Assign a value to @Id (replace 123 with the actual value)

-- Now you can use @Id in your query or stored procedure

SELECT * FROM YourTable WHERE Id = @Id;

If you're using a stored procedure, you would declare the @Id parameter at the beginning of the procedure and make sure it's assigned a value when the procedure is called.

For example:

CREATE PROCEDURE YourStoredProcedure

    @Id INT  -- Declare @Id as a parameter

AS

BEGIN

    -- Your stored procedure logic here

    SELECT * FROM YourTable WHERE Id = @Id;

END;

When calling the stored procedure, make sure to pass a value for the @Id parameter. If you're encountering this error in a programming language like C#, Python, or any other language that interacts with SQL Server, make sure that you're properly assigning a value to the @Id parameter in your code before executing the query or stored procedure.

5 Months