Must declare the scalar variable sql ошибка

@RowFrom int

@RowTo int

are both Global Input Params for the Stored Procedure, and since I am compiling the SQL query inside the Stored Procedure with T-SQL then using Exec(@sqlstatement) at the end of the stored procedure to show the result, it gives me this error when I try to use the @RowFrom or @RowTo inside the @sqlstatement variable that is executed.. it works fine otherwise.. please help.

"Must declare the scalar variable "@RowFrom"."

Also, I tried including the following in the @sqlstatement variable:

'Declare @Rt int'
'SET @Rt = ' + @RowTo

but @RowTo still doesn’t pass its value to @Rt and generates an error.

You can also get this error message if a variable is declared before a GOand referenced after it.

See this question and this workaround.

answered Mar 25, 2019 at 22:11

answered May 24, 2021 at 6:12

Case Sensitivity will cause this problem, too.

@MyVariable and @myvariable are the same variables in SQL Server Man. Studio and will work. However, these variables will result in a «Must declare the scalar variable «@MyVariable» in Visual Studio (C#) due to case-sensitivity differences.

answered Jun 9, 2016 at 11:20

Just an answer for future me (maybe it helps someone else too!). If you try to run something like this in the query editor:

USE [Dbo]
GO

DECLARE @RC int

EXECUTE @RC = [dbo].[SomeStoredProcedure] 
   2018
  ,0
  ,'arg3'
GO

SELECT month, SUM(weight) AS weight, SUM(amount) AS amount 
FROM SomeTable AS e 
WHERE year = @year AND type = 'M'

And you get the error:

Must declare the scalar variable «@year»

That’s because you are trying to run a bunch of code that includes BOTH the stored procedure execution AND the query below it (!). Just highlight the one you want to run or delete/comment out the one you are not interested in.

If someone else comes across this question while no solution here made my sql file working, here’s what my mistake was:

I have been exporting the contents of my database via the ‘Generate Script’ command of Microsofts’ Server Management Studio and then doing some operations afterwards while inserting the generated data in another instance.

Due to the generated export, there have been a bunch of «GO» statements in the sql file.

What I didn’t know was that variables declared at the top of a file aren’t accessible as far as a GO statement is executed. Therefore I had to remove the GO statements in my sql file and the error «Must declare the scalar variable xy» was gone!

answered Oct 19, 2020 at 10:33

As stated in https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-ver16 , the scope of a user-defined variable is batch dependent .

—This will produce the error

 GO   
    DECLARE @MyVariable int;
    SET @MyVariable = 1;
    GO --new batch of code
    SELECT @MyVariable--CAST(@MyVariable AS 
    int);
 GO

—This will not produce the error

 GO   
   DECLARE @MyVariable int;
   SET @MyVariable = 1;
   SELECT @MyVariable--CAST(@MyVariable AS int);
 GO

We get the same error when we try to pass a variable inside a dynamic SQL:

GO
DECLARE @ColumnName VARCHAR(100),
        @SQL NVARCHAR(MAX);
SET @ColumnName = 'FirstName';
EXECUTE ('SELECT [Title],@ColumnName FROM Person.Person');  
GO

—In the case above @ColumnName is nowhere to be found, therefore we can either do:

EXECUTE ('SELECT [Title],' +@ColumnName+ ' FROM Person.Person');

or

GO
DECLARE @ColumnName VARCHAR(100),
        @SQL NVARCHAR(MAX);
SET @ColumnName = 'FirstName';
SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Person';
EXEC sys.sp_executesql @SQL  
GO

answered Sep 15, 2022 at 10:39

Give a ‘GO’ after the end statement and select all the statements then execute

what is wrong with the following query ??

declare  @dbName varchar (50)
declare  @fldName varchar (50)

set  @dbName ='student';
set  @fldName='stID';

declare  @gID smallint
set  @gID =4;


DEClARE @sqlstr nvarchar(max);

SET @sqlstr = 'SELECT  MAX([' + @fldName +'])
  FROM  [dbo].['+ @dbName +'] 
  WHERE [dbo].['+ @dbName +'].[gID ] = @gID';

  EXEC (@sqlstr)

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable

asked Dec 7, 2015 at 11:06

Execution of the variable @sqlStr is done under a separate context than that in which you are currently running. Therefore the variables that you have declared in your session will not be available.

Try this instead:

declare @params = N'@gID smallint';
exec sp_executesql @sqlStr, @params, @gID = @gID;

Documentation for sp_executesql can be found here:

https://msdn.microsoft.com/en-us/library/ms188001.aspx

Full code example:

declare @dbName varchar (50) = 'student';
declare @fldName varchar (50) = 'stID';
declare @gID smallint = 4;
declare @params nvarchar(max) = N'@gID smallint';
declare @sqlstr nvarchar(max) = 
        N'select max([' + @fldName +'])
            from [dbo].['+ @dbName +'] 
           where [dbo].['+ @dbName +'].[gID ] = @gID';
exec sp_executesql @sqlStr, @params, @gID = @gID; 

answered Dec 7, 2015 at 11:17

as brownstone suggested,you have to use sp_executesql,adding one more input .GO operator also has the same behaviour.

Example:

declare @f int
set @f =3

go

select * from test where col1=@f

Above example throws an error,since GO operator is a batch seperator.In Summary,table variable scope is limited to session or batch they are executed

answered Dec 7, 2015 at 12:07