エラーを返すプロシージャがあります。
テーブル変数"@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)からプロシージャを実行すると、エラーが返されます。
問題は、動的な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のいたるところで使用する必要があります)。
このエラーが出るのは、テーブル変数のスコープが単一のバッチに限定されているからで、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
と解釈されることがあります。
あなたのコードを:
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では使用できません。