“必须在存储过程中声明表变量“@name”。

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)执行过程时，它返回一个错误。

4 个解决方案

#1

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

和执行。或者如前所述，将@天才更改为临时表。(你在CREATE #天才中接近的路线，但是你需要在sproc中使用#天才而不是@天才。)

#2

The reason you get this error is that the scope of table variables is limited to a single batch, since sp_executesql runs in its own batch, it has no knowledge that you have declared it in another batch.

您得到这个错误的原因是，表变量的范围仅限于单个批处理，因为sp_executesql在它自己的批处理中运行，它不知道您已经在另一个批处理中声明了它。

It works when you @NotNeededWPRNs is NULL because concatenating NULL yields NULL (unless otherwise set), so you are just executing:

当您@NotNeededWPRNs为空时，它是有效的，因为连接NULL值为NULL(除非另有设置)，所以您正在执行:

exec sp_executesql null;

I would also say, if you are using SQL Server 2008 or later please consider using table valued parameters instead 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);删除表dbo.Prioperties;@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/2013 is 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-MM in some settings.

在不相关的说明上，你应该避免使用文化敏感的日期格式，在这种情况下，6/28/2013显然应该是6月28日，但是如果没有设置DATEFORMAT或语言，你怎么知道这将会在7月6日或6月7日被阅读呢?使用的最佳格式是yyyyMMdd，它从不含糊，即使是ISO标准格式yyyy-MM-dd也可以在某些设置中被解释为yyyy-dd-MM。

#3

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。

#4

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

#1