SQL Server で次のようなストアド プロシージャを作成しようとしています。
入力として整数のリストを取得します (これらの整数が「profile_id」であると仮定します)。 「profile_id」という名前の列を持つすべてのテーブル名を選択します。カーソルに カーソルをループし、パラメータの入力リスト内のいずれかに一致する場合、profile_id 値を出力します。問題は、次のような手順を実行していることです。
EXEC dbo.de_dup '1234,2345';
以下のコメントアウトされた行を実行しようとすると、構文エラーが発生します (手順を参照してください)。
set @id = (select profile_id from @tname where profile_id in @a_profile_id );
質問:
カーソル内の値を実行して設定する正しい方法は何でしょうか? (私たちの場合) このプロシージャに整数のリストを渡す方法は何ですか?これが私の手順です:
ALTER PROCEDURE dbo.de_dup
(@a_profile_id nvarchar(MAX))
AS
DECLARE @tname VARCHAR(max),
@id int;
DECLARE tables_cursor CURSOR FOR
SELECT
a.TABLE_CATALOG +'.'+a.TABLE_SCHEMA + '.'+ a.TABLE_NAME AS table_name
FROM
JobApp.INFORMATION_SCHEMA.COLUMNS a
LEFT OUTER JOIN
JobApp.INFORMATION_SCHEMA.VIEWS b ON a.TABLE_CATALOG = b.TABLE_CATALOG
AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
WHERE
a.COLUMN_NAME = 'profile_id'
GROUP BY
a.TABLE_CATALOG, a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME;
OPEN tables_cursor;
FETCH NEXT FROM tables_cursor INTO @tname;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @a_profile_id ;
PRINT @tname ;
--set @id= (select profile_id from @tname where profile_id in @a_profile_id );
--PRINT 'id : ' + @id;
FETCH NEXT FROM tables_cursor INTO @tname;
END;
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO;
プリもっと詳しく説明したほうがよいでしょうか。教えてください。よろしくお願いします。
最初の質問は、カーソルを使用する理由です。
– トム A2020 年 9 月 4 日 19:51
これは、カーソルを使用して行う、私が提供した最も単純な例です。各テーブル名を使用して、より複雑な処理を実行します。
– ビクターグラム2020 年 9 月 4 日 19:54
エラーを投稿していただけますか?テーブル名が変数に含まれるクエリを実行する場合は、動的 SQL の概念を使用する必要があります。
– ソウミャダール・グリシェッティ2020 年 9 月 4 日 20:01
プロファイル ID をそのまま印刷したいですか? SQLとは何ですか?サーバーのバージョン?
– ソウミャダール・グリシェッティ2020 年 9 月 4 日 20:04
クエリで使用する受信プロファイル ID を使用したいと考えています: (select profile_id from tname where profile_id in a_profile_id)。 --- 例: "select profile_id from dao.client where profile_id in (1234,2345) "
– ビクターグラム2020 年 9 月 4 日 20:11
----------------------------------このソリューションは動的 SQL を使用しています。私の知る限り、変数にテーブル名がある場合は動的 SQL を使用する必要があります。
DBFIDDLE の動作コード
クエリ:
CREATE PROCEDURE dbo.de_dup (@a_profile_id NVARCHAR(MAX))
AS
BEGIN
DECLARE @tname VARCHAR(max)
,@id INT
,@dynamicSQL NVARCHAR(MAX);
DECLARE @matched_tables TABLE (Name NVARCHAR(255));
DECLARE @matched_profileIds TABLE (profile_id INT);
DECLARE @profile_ids NVARCHAR(MAX) = @a_profile_id
INSERT INTO @matched_tables
SELECT DISTINCT a.TABLE_SCHEMA + '.' + a.TABLE_NAME AS table_name
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE a.COLUMN_NAME = 'profile_id'
WHILE EXISTS (
SELECT 1
FROM @matched_tables
)
BEGIN
SELECT TOP 1 @tname = [Name]
FROM @matched_tables
SET @dynamicSQL = CONCAT (
'select profile_id from '
,@tname
,' WHERE '
,''','
,@profile_ids
,','''
,' LIKE '
,'''%,'
,''''
,' + CAST(profile_id AS NVARCHAR(MAX)) + '
,''',%'
,''''
)
PRINT @dynamicSQL;
INSERT INTO @matched_profileIds
EXEC (@dynamicSQL)
DELETE
FROM @matched_tables
WHERE [Name] = @tname
END
SELECT *
FROM @matched_profileIds
END
形成される動的 SQL は次のとおりです
プロファイル ID を選択します FROM dbo.TestTable WHERE ',123,456,789,1011,1213,' LIKE '%,' + CAST(profile_id AS NVARCHAR(MAX)) + ',%'
そこで、Split と呼ばれるテーブル値関数を使用して同様の問題を解決しました。区切り文字で区切られたリストをテーブル内の行に分割し、結合したり、コード内でサブクエリとして使用したりできます。
CREATE FUNCTION [dbo].[Split]
(
@char_array varchar(500), @delimiter char(1)
)
RETURNS
@parsed_array table
(
Parsed varchar(50)
)
AS
BEGIN
DECLARE @parsed varchar(50), @pos int
SET @char_array = LTRIM(RTRIM(@char_array))+ @delimiter
SET @pos = CHARINDEX(@delimiter, @char_array, 1)
IF REPLACE(@char_array, @delimiter, '') <> ''
BEGIN
WHILE @pos > 0
BEGIN
SET @parsed = LTRIM(RTRIM(LEFT(@char_array, @pos - 1)))
IF @parsed <> ''
BEGIN
INSERT INTO @parsed_array (Parsed)
VALUES (@parsed)
END
SET @char_array = RIGHT(@char_array, LEN(@char_array) - @pos)
SET @pos = CHARINDEX(@delimiter, @char_array, 1)
END
END
RETURN
END
GO
次のように使用します
SELECT f.Parsed INTO #s FROM dbo.Split(@a_profile_id, ',') f;
次にクエリ内に記述します (簡潔にするために関連部分のみ)
select profile_id from @tname where profile_id in(select Parsed from #s);
set @id= を省略したのは、select ステートメントが複数の結果を返す場合に @id の値に対して予測できない結果が生じるためです。しかし、いずれにしても、これは実際のコードではないことがわかりましたので...
免責事項: Split 関数の内容はオンラインで他の人から入手しました。誰を思い出せたら、それを正しく帰属させるでしょう。
2
SQL Server のバージョンが 2016 以降の場合は、STRING_SPLIT を使用できます。
– ソウミャダール・グリシェッティ2020 年 9 月 4 日 22:00
@SowmyadharGourishetty ありがとう、その組み込み関数は私が使っていた古い関数よりも優れています!
– CB_ロン2020 年 9 月 4 日 23:22