Msgstr "Должен объявить переменную таблицы" @name "" в хранимой процедуре

У меня есть процедура, которая возвращает ошибку:

Должен объявить переменную таблицы "@PropIDs".

Но за ним следует сообщение:

(затронуто 123 строки (строк))

Ошибка появляется, когда я выполняю ее с помощью

EXEC [dbo].[GetNeededProperties] '1,3,5,7,2,12', '06/28/2013', 'TT'

Но отлично работает, когда

EXEC [dbo].[GetNeededProperties] NULL, '06/28/2013', 'TT'

Может кто-нибудь мне помочь? Процедура:

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)

Я нашел свое решение при объявлении таблицы следующим образом:

IF OBJECT_ID('#PropIDs', 'U') IS NOT NULL
  DROP TABLE #PropIDs

CREATE  TABLE  #PropIDs

Но когда вы выполняете процедуру с С# (linq sql), она возвращает ошибку

Ответ 1

Проблема заключается в том, что вы смешиваете динамический SQL с нединамическим SQL.

Во-первых - причина, по которой он работает, когда вы помещаете NULL в @NotNeededWPRN, потому что, когда эта переменная равна NULL, ваш @ProductsSQL становится NULL.

Что вам нужно сделать, либо сделайте таблицу @PropsIDs переменной не таблицы, либо временную таблицу или физическую таблицу. ИЛИ вам нужно обернуть все в динамический SQL и выполнить его.

Таким образом, простой способ - сделать что-то вроде этого:

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

и выполнить это. ИЛИ, как указано, измените @ProdID на временную таблицу. (Маршрут, к которому вы приближаетесь в CREATE #ProdIds, но тогда вам нужно использовать #ProdID вместо @ProdID везде в sproc).

Ответ 2

Причина, по которой вы получаете эту ошибку, состоит в том, что область переменных таблицы ограничена одной партией, так как sp_executesql работает в своей собственной партии, она не знает, что вы объявили ее в другой партии.

Это работает, когда вы @NotNeededWPRNs NULL, потому что конкатенация NULL дает NULL (если не установлено иное), поэтому вы просто выполняете:

exec sp_executesql null;

Я бы также сказал, что если вы используете SQL Server 2008 или более позднюю версию , пожалуйста, рассмотрите возможность использования табличных параметров ограниченного списка строк. Это намного безопаснее и эффективнее, и проверяет ввод, если бы я прошел 1); DROP TABLE dbo.Prioperties; -- как @NotNeededWPRNs, вы могли бы найти себя без таблицы свойств.

Сначала вам нужно будет создать тип (я обычно использую универсальное имя для повторного использования):

CREATE TYPE dbo.IntegerList TABLE (Value INT);

Затем вы можете добавить его в свою процедуру:

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)

В отношении несвязанной заметки вам следует избегать использования форматов дат культуры, когда это возможно, 06/28/2013, очевидно, должно быть 28 июня в этом случае, но как насчет 06/07/2013 без установки DATEFORMAT или языка, как знаете ли вы, что это будет прочитано с 6 июля или 7 июня? Лучший формат для использования - yyyyMMdd, он никогда не бывает двусмысленным, даже стандартный формат ISO yyyy-MM-dd можно интерпретировать как yyyy-dd-MM в некоторых настройках.

Ответ 3

Измените свой код на:

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

Переменная таблицы, объявленная вне динамического SQL, не будет доступна динамическому SQL.

Ответ 4

Вы можете избежать использования динамического sql, создав sql-функцию, использующую CTE (я нашел код, приведенный много лет назад на sqlservercentral - Amit Gaur):

Измените тело своих 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, ','))

Ниже кода 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