I wrote this SQL in a stored procedure but not working,
declare @tableName varchar(max) = 'TblTest'
declare @col1Name varchar(max) = 'VALUE1'
declare @col2Name varchar(max) = 'VALUE2'
declare @value1 varchar(max)
declare @value2 varchar(200)
execute('Select TOP 1 @value1='+@col1Name+', @value2='+@col2Name+' From '+ @tableName +' Where ID = 61')
select @value1
execute('Select TOP 1 @value1=VALUE1, @value2=VALUE2 From TblTest Where ID = 61')
This SQL throws this error:
Must declare the scalar variable "@value1".
I am generating the SQL dynamically and I want to get value in a variable. What should I do?
This question is related to
sql-server
tsql
stored-procedures
executequery
Here is a simple example :
Create or alter PROCEDURE getPersonCountByLastName (
@lastName varchar(20),
@count int OUTPUT
)
As
Begin
select @count = count(personSid) from Person where lastName like @lastName
End;
Execute below statements in one batch (by selecting all)
1. Declare @count int
2. Exec getPersonCountByLastName kumar, @count output
3. Select @count
When i tried to execute statements 1,2,3 individually, I had the same error. But when executed them all at one time, it worked fine.
The reason is that SQL executes declare, exec statements in different sessions.
Open to further corrections.
This will occur in SQL Server as well if you don't run all of the statements at once. If you are highlighting a set of statements and executing the following:
DECLARE @LoopVar INT
SET @LoopVar = (SELECT COUNT(*) FROM SomeTable)
And then try to highlight another set of statements such as:
PRINT 'LoopVar is: ' + CONVERT(NVARCHAR(255), @LoopVar)
You will receive this error.
You can't concatenate an int to a string. Instead of:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;
You need:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);
To help illustrate what's happening here. Let's say @RowTo = 5.
DECLARE @RowTo int;
SET @RowTo = 5;
DECLARE @sql nvarchar(max);
SET @sql = N'SELECT ' + CONVERT(varchar(12), @RowTo) + ' * 5';
EXEC sys.sp_executesql @sql;
In order to build that into a string (even if ultimately it will be a number), I need to convert it. But as you can see, the number is still treated as a number when it's executed. The answer is 25, right?
In your case you don't really need to re-declare @Rt etc. inside the @sql string, you just need to say:
SET @sql = @sql + ' WHERE RowNum BETWEEN '
+ CONVERT(varchar(12), @RowFrom) + ' AND '
+ CONVERT(varchar(12), @RowTo);
Though it would be better to have proper parameterization, e.g.
SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
EXEC sys.sp_executesql @sql,
N'@RowFrom int, @RowTo int',
@RowFrom, @RowTo;
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.
You can also get this error message if a variable is declared before a GO
and referenced after it.
See this question and this workaround.
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!
Just adding what fixed it for me, where misspelling is the suspect as per this MSDN blog...
When splitting SQL strings over multiple lines, check that that you are comma separating your SQL string from your parameters (and not trying to concatenate them!) and not missing any spaces at the end of each split line. Not rocket science but hope I save someone a headache.
For example:
db.TableName.SqlQuery(
"SELECT Id, Timestamp, User " +
"FROM dbo.TableName " +
"WHERE Timestamp >= @from " +
"AND Timestamp <= @till;" + [USE COMMA NOT CONCATENATE!]
new SqlParameter("from", from),
new SqlParameter("till", till)),
.ToListAsync()
.Result;
Just FYI, I know this is an old post, but depending on the database COLLATION settings you can get this error on a statement like this,
SET @sql = @Sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
if for example you typo the S in the
SET @sql = @***S***ql
sorry to spin off the answers already posted here, but this is an actual instance of the error reported.
Note also that the error will not display the capital S in the message, I am not sure why, but I think it is because the
Set @sql =
is on the left of the equal sign.
This is most likely not an answer to the issue itself but this question pops up as first result when searching for Sql declare scalar variable
hence i share a possible solution to this error.
In my case this error was caused by the use of ;
after a SQL statement. Just remove it and the error will be gone.
I guess the cause is the same as @IronSean already posted in an comment above:
it's worth noting that using GO (or in this case ;) causes a new branch where declared variables aren't visible past the statement.
For example:
DECLARE @id int
SET @id = 78
SELECT * FROM MyTable WHERE Id = @var; <-- remove this character to avoid the error message
SELECT * FROM AnotherTable WHERE MyTableId = @var
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.
-- CREATE OR ALTER PROCEDURE
ALTER PROCEDURE out (
@age INT,
@salary INT OUTPUT)
AS BEGIN
SELECT @salary = (SELECT SALARY FROM new_testing where AGE = @age ORDER BY AGE OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY);
END
-----------------DECLARE THE OUTPUT VARIABLE---------------------------------
DECLARE @test INT
---------------------THEN EXECUTE THE QUERY---------------------------------
EXECUTE out 25 , @salary = @test OUTPUT
print @test
-------------------same output obtain without procedure------------------------------------------- SELECT * FROM new_testing where AGE = 25 ORDER BY AGE OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
Declare @v1 varchar(max), @v2 varchar(200);
Declare @sql nvarchar(max);
Set @sql = N'SELECT @v1 = value1, @v2 = value2
FROM dbo.TblTest -- always use schema
WHERE ID = 61;';
EXEC sp_executesql @sql,
N'@v1 varchar(max) output, @v2 varchar(200) output',
@v1 output, @v2 output;
You should also pass your input, like wherever 61 comes from, as proper parameters (but you won't be able to pass table and column names that way).
Source: Stackoverflow.com