ストアドプロシージャ内でテーブル変数 "@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

しかし、C# (linq sql)からプロシージャを実行すると、エラーが返されます。

Albireo
編集された質問 28日 11月 2013 в 11:44
プログラミング
sql sql-server
28日 11月 2013 в 11:30
まだビューはありません
ソリューション

問題は、動的なSQLと非動的なSQLを混同していることです。

まず、@NotNeededWPRNsにNULLを入れても動作するのは、その変数が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)
    '

を実行することです。 または、前述の通り、@ProdIDsを一時的なテーブルに変更します。(CREATE #ProdIdsでアプローチしているルートですが、その場合は、@ProdIDの代わりに#ProdIDをsprocのいたるところで使用する必要があります)。

解説 (1)

このエラーが出るのは、テーブル変数のスコープが単一のバッチに限定されているからで、sp_executesqlは自分のバッチの中で実行されるので、他のバッチで宣言したことを知らないのです。

また、@NotNeededWPRNs が NULL の場合は、NULL を連結すると NULL になるので（特に設定されていない限り）、単に実行しているだけなので動作します。

exec sp_executesql null;

また、SQL Server 2008以降を使用している場合は、必ず文字列の区切りリストではなく、table valued parametersの使用を検討してください。これははるかに安全で効率的であり、入力を検証します。もし私が 1); DROP TABLE dbo.Prioperties; -- を @NotNeededWPRNs として渡すと、properties テーブルがないことに気づくかもしれません。

まず、型を作成する必要があります（私は再利用性のために一般的な名前を使う傾向があります）。

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は明らかに6月28日とされていますが、06/07/2013はどうでしょうか。DATEFORMATや言語を設定せずに、これが7月6日と読まれるのか、6月7日と読まれるのかをどうやって知ることができますか？ISO標準フォーマットであるyyyy-MM-ddでさえ、設定によってはyyyy-dd-MMと解釈されることがあります。

GarethD
編集した答え 28日 11月 2013 в 11:54
解説 (1)

あなたのコードを:

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では使用できません。

解説 (3)