SQL 必须声明标量变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7181976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Must declare the scalar variable
提问by bill
@RowFrom int
@RowFrom int
@RowTo 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 @RowFromor @RowToinside the @sqlstatementvariable that is executed.. it works fine otherwise.. please help.
都是存储过程的全局输入参数，并且由于我在存储过程中使用 T-SQL 编译 SQL 查询，然后Exec(@sqlstatement)在存储过程的末尾使用以显示结果，因此当我尝试使用时，它给了我这个错误在@RowFrom或@RowTo内@sqlstatement被执行的变量..它工作正常，否则..请帮助。
"Must declare the scalar variable "@RowFrom"."
Also, I tried including the following in the @sqlstatementvariable:
另外，我尝试在@sqlstatement变量中包含以下内容：
'Declare @Rt int'
'SET @Rt = ' + @RowTo
but @RowTostill doesn't pass its value to @Rtand generates an error.
但@RowTo仍然没有将其值传递给@Rt并产生错误。
采纳答案by Aaron Bertrand
You can't concatenate an int to a string. Instead of:
您不能将 int 连接到字符串。代替：
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.
为了帮助说明这里发生的事情。假设@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?
为了将其构建为字符串（即使最终它将是一个数字），我需要将其转换。但是正如您所看到的，该数字在执行时仍被视为数字。答案是 25，对吧？
In your case you don't really need to re-declare @Rt etc. inside the @sql string, you just need to say:
在你的情况下，你真的不需要在 @sql 字符串中重新声明 @Rt 等，你只需要说：
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;
回答by htm11h
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,
仅供参考，我知道这是一篇旧帖子，但根据数据库 COLLATION 设置，您可能会在这样的语句中收到此错误，
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
另请注意，错误不会在消息中显示大写的 S，我不确定为什么，但我认为这是因为
Set @sql =
is on the left of the equal sign.
在等号的左边。
回答by Pierre C
You can also get this error message if a variable is declared before a GOand referenced after it.
如果变量在 a 之前声明并在 aGO之后引用，您也会收到此错误消息。
See this questionand this workaround.
回答by Tim Tyler
Just adding what fixed it for me, where misspelling is the suspect as per this MSDN blog...
只需添加为我修复的内容，根据此 MSDN 博客，拼写错误是嫌疑人...
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.
将 SQL 字符串拆分为多行时，请检查您是否使用逗号将 SQL 字符串与参数分隔开（而不是尝试连接它们！）并且每个拆分行的末尾没有丢失任何空格。不是火箭科学，但希望我能救人头疼。
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;
回答by saiyancoder
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"
必须声明标量变量“@year”
That's because you are trying to run a bunch of code that includes BOTHthe stored procedure execution ANDthe query below it (!). Just highlight the one you want to run or delete/comment out the one you are not interested in.
那是因为你正试图运行一串代码，其中包括BOTH执行存储过程和它下面的查询（！）。只需突出显示您要运行的那个或删除/注释掉您不感兴趣的那个。
回答by Hans M Ohio
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.
@MyVariable 和 @myvariable 在 SQL Server Man 中是相同的变量。工作室，将工作。但是，由于区分大小写的差异，这些变量将导致“必须在 Visual Studio (C#) 中声明标量变量“@MyVariable”。
