SQL “必须在存储过程中声明表变量“@name”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20265023/
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 table variable "@name"" in stored procedure
提问by Tom
I have a procedure which returns the error:
我有一个返回错误的过程：
Must declare the table variable "@PropIDs".
必须声明表变量“@PropIDs”。
But it is followed with the message:
但紧随其后的是消息：
(123 row(s) affected)
（123 行受影响）
The error appears when I execute it with
当我执行它时出现错误
EXEC [dbo].[GetNeededProperties] '1,3,5,7,2,12', '06/28/2013', 'TT'
But works fine when
但工作正常时
EXEC [dbo].[GetNeededProperties] NULL, '06/28/2013', 'TT'
Can any one help me with that? The procedure:
任何人都可以帮助我吗？步骤：
CREATE PROCEDURE [dbo].[GetNeededProperties]
@NotNeededWPRNs nvarchar(max), --string like '1,2,3,4,5'
@LastSynch datetime,
@TechCode varchar(5)
AS
BEGIN
DECLARE @PropIDs TABLE
(ID bigint)
Declare @ProductsSQL nvarchar(max);
SET @ProductsSQL = 'Insert into @PropIDs (ID)
SELECT [WPRN] FROM [dbo].[Properties] WHERE(WPRN in (' + @NotNeededWPRNs + '))'
exec sp_executesql @ProductsSQL
SELECT p.WPRN AS ID,
p.Address AS Address,
p.Address AS Street
FROM [dbo].[Properties] AS p
WHERE
p.WPRN NOT IN( SELECT ID FROM @PropIDs)
I've found kind of solution when declaring table like this:
我在声明这样的表时找到了一种解决方案：
IF OBJECT_ID('#PropIDs', 'U') IS NOT NULL
DROP TABLE #PropIDs
CREATE TABLE #PropIDs
But when execute the procedure from C# (linq sql) it returns an error
但是当从 C# (linq sql) 执行过程时它返回一个错误
回答by Allan S. Hansen
The issue is that you're mixing up dynamic SQL with non-dynamic SQL.
问题是您将动态 SQL 与非动态 SQL 混为一谈。
Firstly - the reason it works when you put NULL into @NotNeededWPRNs is because when that variable is NULL, your @ProductsSQL becomes NULL.
首先 - 当您将 NULL 放入 @NotNeededWPRNs 时它起作用的原因是因为当该变量为 NULL 时，您的 @ProductsSQL 变为 NULL。
WHat you need to do is either make your @PropsIDs table a non-table variable and either a temporary table or a physical table. OR you need to wrap everything in dynamic SQL and execute it.
你需要做的是让你的@PropsIDs 表成为一个非表变量和一个临时表或一个物理表。或者您需要将所有内容包装在动态 SQL 中并执行它。
So the easy way is to do something like this:
所以简单的方法是做这样的事情：
Declare @ProductsSQL nvarchar(max);
SET @ProductsSQL = '
DECLARE @PropIDs TABLE
(ID bigint)
Insert into @PropIDs (ID)
SELECT [WPRN] FROM [dbo].[Properties] WHERE(WPRN in (' + @NotNeededWPRNs + '))
SELECT p.WPRN AS ID,
p.Address AS Address,
p.Address AS Street
FROM [dbo].[Properties] AS p
WHERE
p.WPRN NOT IN( SELECT ID FROM @PropIDs)
'
and execute that. OR as mentioned - change @ProdIDs to a temporary table. (The route you're approaching in the CREATE #ProdIds, but then you need to use #ProdIDs instead of @ProdIDs everywhere in the sproc).
并执行那个。或者如前所述 - 将@ProdIDs 更改为临时表。（您在 CREATE #ProdIds 中接近的路线，但随后您需要在 sproc 中的任何地方使用 #ProdIDs 而不是 @ProdIDs）。
回答by GarethD
The reason you get this error is that the scope of table variables is limited to a single batch, since sp_executesqlruns in its own batch, it has no knowledge that you have declared it in another batch.
您收到此错误的原因是表变量的范围仅限于单个批次，因为sp_executesql在其自己的批次中运行，因此它不知道您已在另一批次中声明它。
It works when you @NotNeededWPRNsis NULLbecause concatenating NULLyields NULL(unless otherwise set), so you are just executing:
它在您@NotNeededWPRNs是NULL因为连接NULL产量NULL（除非另有设置）时起作用，所以您只是在执行：
exec sp_executesql null;
I would also say, if you are using SQL Server 2008 or later pleaseconsider using table valued parametersinstead of a delimited list of strings. This is much safer and more efficient, and validates the input, if I were to pass 1); DROP TABLE dbo.Prioperties; --as @NotNeededWPRNs, you could find yourself without a properties table.
我还要说，如果您使用的是 SQL Server 2008 或更高版本，请考虑使用表值参数而不是分隔的字符串列表。这更安全、更有效，并验证输入，如果我传递1); DROP TABLE dbo.Prioperties; --as @NotNeededWPRNs，您可能会发现自己没有属性表。
First you would need to create the type (I tend to use a generic name for reusability):
首先，您需要创建类型（为了可重用，我倾向于使用通用名称）：
CREATE TYPE dbo.IntegerList TABLE (Value INT);
Then you can add it to your procedure:
然后您可以将其添加到您的程序中：
CREATE PROCEDURE [dbo].[GetNeededProperties]
@NotNeededWPRNs dbo.IntegerList READONLY,
@LastSynch DATETIME,
@TechCode VARCHAR(5)
AS
BEGIN
SELECT p.WPRN AS ID,
p.Address AS Address,
p.Address AS Street
FROM [dbo].[Properties] AS p
WHERE p.WPRN NOT IN (SELECT Value FROM @NotNeededWPRNs)
On an unrelated note, you should avoid using culture sensitive date formats where possible, 06/28/2013is clearly supposed to be 28th June in this case, but what about 06/07/2013, without setting DATEFORMAT, or the language how do you know if this will be read as 6th July or 7th June? The best format to use is yyyyMMdd, it is never ambiguous, even the ISO standard format yyyy-MM-dd can be interpreted as yyyy-dd-MMin some settings.
在一个不相关的说明中，您应该尽可能避免使用文化敏感的日期格式，06/28/2013在这种情况下显然应该是 28th June，但是如果06/07/2013没有设置DATEFORMAT，或者语言如何知道这是否会被读取为 6th July 或6 月 7 日？最好使用的格式是yyyyMMdd，它永远不会有歧义，即使是 ISO 标准格式 yyyy-MM-dd 也可以yyyy-dd-MM在某些设置中解释为。
回答by Raj
Change you code to :
将您的代码更改为：
Declare @ProductsSQL nvarchar(max);
SET @ProductsSQL = 'DECLARE @PropIDs TABLE
(ID bigint);
Insert into @PropIDs (ID)
SELECT [WPRN] FROM [dbo].[Properties] WHERE(WPRN in (' + @NotNeededWPRNs + '))'
exec sp_executesql @ProductsSQL
Table variable declared outside the dynamic SQL will not be available to the dynamic SQL.
在动态 SQL 之外声明的表变量将不可用于动态 SQL。
回答by user3041160
You can avoid using dynamic sql by creating a sql function that use a CTE (I found the code below many years ago on sqlservercentral - Amit Gaur) :
您可以通过创建一个使用 CTE 的 sql 函数来避免使用动态 sql（我多年前在 sqlservercentral - Amit Gaur 上找到了下面的代码）：
Change the body of your procs with something like this :
用这样的东西改变你的 procs 的主体：
SELECT p.WPRN AS ID,
p.Address AS Address,
p.Address AS Street
FROM [dbo].[Properties] AS p
WHERE
p.WPRN NOT IN ( SELECT item FROM dbo.strToTable(@NotNeededWPRNs, ','))
Below the sql code that transforms a string into a table :
在将字符串转换为表的 sql 代码下方：
CREATE FUNCTION [dbo].[strToTable]
(
@array varchar(max),
@del char(1)
)
RETURNS
@listTable TABLE
(
item int
)
AS
BEGIN
WITH rep (item,list) AS
(
SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item,
SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list
UNION ALL
SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item,
SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list
FROM rep
WHERE LEN(rep.list) > 0
)
INSERT INTO @listTable
SELECT item FROM rep
RETURN
END
