T-SQLストアドプロシージャで複数のID値を受け取るその他の方法

T-SQL ストアドプロシージャ：複数のID値を受け取る

実装方法

以下のコード例は、複数のID値を受け取り、それらのIDを持つレコードをデータベースから検索するストアドプロシージャです。

USE [DatabaseName]
GO

CREATE PROCEDURE [dbo].[GetRecordsByIds]
(
    @IdList varchar(max)
)
AS
BEGIN
    DECLARE @Id int

    WHILE PATINDEX('%[^,]+%', @IdList) > 0
    BEGIN
        SELECT @Id = LEFT(@IdList, PATINDEX('%[^,]+%', @IdList) - 1)

        -- 検索処理
        SELECT * FROM [TableName] WHERE [Id] = @Id

        SET @IdList = RIGHT(@IdList, LEN(@IdList) - PATINDEX('%[^,]+%', @IdList))

        -- 次のID処理へ
    END
END
GO

解説

1. USE [DatabaseName]：使用するデータベースを指定します。
2. CREATE PROCEDURE：ストアドプロシージャの作成を開始します。
3. [dbo].[GetRecordsByIds]：ストアドプロシージャの名前を指定します。
4. @IdList varchar(max)：ストアドプロシージャの引数として、カンマ区切りのID値を受け取る変数を定義します。
5. WHILE PATINDEX('%[^,]+%', @IdList) > 0：@IdListにカンマがまだ存在する間、処理を繰り返します。
6. SELECT @Id = LEFT(@IdList, PATINDEX('%[^,]+%', @IdList) - 1)：@IdListの先頭から最初のカンマまでの文字列を@Id変数に格納します。
7. -- 検索処理：@Id変数に格納されたID値を持つレコードをデータベースから検索します。
8. SET @IdList = RIGHT(@IdList, LEN(@IdList) - PATINDEX('%[^,]+%', @IdList))：@IdListから最初のカンマまでの文字列を削除します。
9. -- 次のID処理へ：ループ処理の次のIDへ移動します。
10. END：ストアドプロシージャの終了を示します。

応用例

• 複数の商品IDを受け取り、商品情報を表示する。

注意点

• ストアドプロシージャに渡されるID値は、安全な方法で検証する必要があります。
• 大量のID値を受け取る場合、処理時間が長くなる可能性があります。

USE [AdventureWorks2019]
GO

EXEC [dbo].[GetProductsByIds]
'1,2,3,4,5'

このコードは、AdventureWorks2019データベースのProductsテーブルから、ID 1, 2, 3, 4, 5 の商品情報を取得し、表示します。

USE [CustomerDatabase]
GO

DECLARE @IdList varchar(max)

SET @IdList = '10,20,30,40,50'

EXEC [dbo].[UpdateCustomersByIds]
@IdList,
'New Address'

このコードは、CustomerDatabaseデータベースのCustomersテーブルの、ID 10, 20, 30, 40, 50 の顧客の住所を「New Address」に更新します。

USE [OrdersDatabase]
GO

EXEC [dbo].[GetOrderDetailsByIds]
'1234,5678,9012'

パラメータの型

エラー処理

ストアドプロシージャの実行中にエラーが発生した場合、エラーメッセージを返せるように処理を記述する必要があります。

テーブル型パラメータ

テーブル型パラメータを使用すると、複数のID値を構造化されたデータとして渡すことができます。

USE [DatabaseName]
GO

CREATE TYPE [dbo].[IdList] AS TABLE
(
    Id int
)

CREATE PROCEDURE [dbo].[GetRecordsByIds]
(
    @IdList [dbo].[IdList] READONLY
)
AS
BEGIN
    FOR EACH @Id IN @IdList
    BEGIN
        -- 検索処理
        SELECT * FROM [TableName] WHERE [Id] = @Id.Id
    END
END
GO

このコード例では、IdListというテーブル型パラメータを作成し、Id列を定義しています。ストアドプロシージャに渡されるID値は、このテーブル型パラメータに格納されます。

XML

XMLを使用して、複数のID値を1つの文字列として渡すことができます。

USE [DatabaseName]
GO

CREATE PROCEDURE [dbo].[GetRecordsByIds]
(
    @IdList xml
)
AS
BEGIN
    DECLARE @Id int

    SELECT @IdList = CONVERT(xml, '<IdList>' + REPLACE(@IdList, ',', '</Id><Id>') + '</IdList>')

    WHILE @IdList.exist('//Id') > 0
    BEGIN
        SELECT @Id = @IdList.value('(//Id)[1]', 'int')

        -- 検索処理
        SELECT * FROM [TableName] WHERE [Id] = @Id

        DELETE @IdList.value('(//Id)[1]', 'int')
    END
END
GO

このコード例では、XML文字列を使用してID値を格納しています。ストアドプロシージャ内では、XPathを使用してID値を個別に抽出します。

複数行のテーブル変数

複数行のテーブル変数を使用して、複数のID値を格納することができます。

USE [DatabaseName]
GO

DECLARE @IdList TABLE
(
    Id int
)

INSERT INTO @IdList (Id) VALUES (1), (2), (3), (4), (5)

EXEC [dbo].[GetRecordsByIds]
@IdList

どの方法を選択する？

どの方法を選択するかは、状況によって異なります。

• 構造化されたデータを渡したい場合は、テーブル型パラメータを使用します。
• 複数のID値を1つの文字列として渡したい場合は、XMLを使用します。
• 処理速度を重視する場合は、複数行のテーブル変数を使用します。