【解決方法】TSQL クエリ: in 句の sp にパラメーターを渡す

[ad_1]

IN句を使用しているパラメータを渡す必要があるspがあります

以下のクエリは正常に動作しています

SQL

select name from tbl where Id='001' and locationId IN (1,2,3))

しかし、このクエリはエラーを出しています

SQL

Declare @var NVARCHAR(100) = '1,2,3';
Declare @whereClause NVARCHAR(100);
SET @whereClause = 'where Id='001' and locationId IN ('+@Var+')';
Select name from tbl + @whereClause

これは私が得ているエラーです
nvarchar 値 IN (1,2,3) ‘ をデータ型 int に変換するときに変換に失敗しました。

私が間違えたところを助けてください

助けてくれてありがとう

私が試したこと：

私がしようとしたコード

SQL

Declare @var NVARCHAR(100) = '1,2,3';
Select name from tbl where Id='001' and locationId IN (@var)

解決策 1
解決策 2
解決策 4
解決策 3

解決策 1

その方法で試す場合は、動的 SQL を使用する必要があります。

この CP 投稿に投稿された解決策とリンクをご覧ください … SQLパラメータの文字列配列をSQLのIN句に渡す方法[^] 解決策の選択

[EDIT] 試した方法で、SQL と動的 SQL を混在させることはできません。
必要なものを変える

Declare @var NVARCHAR(100) = '1,2,3';
Declare @whereClause NVARCHAR(100);
SET @whereClause = 'where Id=''001'' and locationId IN (' + @Var + ')';

Declare @sql nvarchar(max) = 'Select name from tbl '+ @whereClause

print @sql -- Note this prints "Select name from tbl where Id='001' and locationId IN (1,2,3)"

EXEC sp_executesql @sql

上記のリンクにはより良い例があります

解決策 2

これを行う簡単な方法は、ステートメントで SQL SPLIT 関数を使用することです。この機能は SQL Server 2016 で追加されました。以前のバージョンでは ユーザー定義関数 インストールされていること。この場合、Sql Server Central などのサイトで利用できるものがたくさんあります。
よく読んだ: “Numbers” または “Tally” テーブル: それが何であり、ループをどのように置き換えるか[^]

このステートメントは、2016+ 組み込み関数を使用しています。

SQL

DECLARE @ID NVARCHAR(3) = '001'
DECLARE @LocationIDs NVARCHAR(100) = '1,2,3'

SELECT Name
FROM   Table
WHERE  Id = @ID
AND    LocationID IN (SELECT value FROM STRING_SPLIT(@LocationIDs, ','))

2 番目のオプションはもう少し複雑で、ADO 呼び出し内に SQL ユーザー定義型と異なる構文が必要です。利点は、DataTables、DataReaders、iEnumerables などのより複雑な型を渡すことができることです。

SQL 部分

SQL

CREATE TYPE dbo.UserTableType_INTs AS TABLE( IntValue INT NOT NULL)
GO
CREATE PROCEDURE DBO.usp_MyProcedure (
  @ID          NVARCHAR(100),
  @LocationIDs dbo.UserTableType_INTs READONLY
) AS
BEGIN
  SELECT Name
  FROM   Table
  WHERE  Id = @ID
  AND    LocationID IN @LocationIDs
END
GO

そして、C# で ADO 経由で呼び出す

// Sample datatable, I generally don't do this and my syntax may be off
DataTable LocationIDs = new DataTable();
LocationIDs.Clear();
LocationIDs.Columns.Add("IntValue");
LocationIDs.Rows.Add(1);
LocationIDs.Rows.Add(2);
LocationIDs.Rows.Add(3);

SqlCommand cmd = new SqlCommand(sqlInsert, connection);
cmd.CommandType = CommandType.StoredProcedure;

// standard input parameter shortcut
cmd.Parameters.AddWithValue("@ID", "001"); 

// different parameter addition when using an SQL User Type
SqlParameter tvpParam = cmd.Parameters.AddWithValue("@LocationIDs", LocationIDs); 
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.UserTableType_INTs";

解決策 4

これは素晴らしい答えです。

SELECT 値 FROM STRING_SPLIT(@LocationIDs, ‘,’)

私はたくさん検索してきましたが、これが私が得た最良の解決策でした。どうもありがとう

解決策 3

動的 SQL でない限り、IN ステートメントで @var を使用することはできません。

Declare @var NVARCHAR(100) = '1,2,3';
declare @sql varchar(300) = 'SELECT * FROM TBL WHERE ID IN (' + @var + ');';
exec(@sql)

または、文字列の分割関数を使用して @var を分割し、直接使用することもできます。

[ad_2]