EXECUTE AS を活用した高度なテクニック：SQL Server で EXEC 結果を SQL 変数に格納

2024-05-26

SQL Server で EXEC 結果を SQL 変数に割り当てる方法

OUTPUT パラメータを使用する

EXEC プロシージャに OUTPUT パラメータを定義することで、プロシージャの戻り値を SQL 変数に直接割り当てることができます。

DECLARE @result INT;

EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2', @result OUTPUT;

SELECT @result;

この例では、MyStoredProcedure プロシージャは @result という OUTPUT パラメータを持ち、このパラメータにはプロシージャの戻り値が格納されます。 SELECT @result; ステートメントは、プロシージャの実行後に @result 変数の値を出力します。

SELECT ステートメントを使用する

EXEC プロシージャの戻り値を格納する临时表を作成し、その表を SELECT ステートメントを使用して SQL 変数に格納することができます。

DECLARE @result INT;

CREATE TABLE #temp (result INT);

EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2', @result OUTPUT INSERT INTO #temp;

SELECT @result = result FROM #temp;

DROP TABLE #temp;

この例では、MyStoredProcedure プロシージャは @result という OUTPUT パラメータを持ち、このパラメータにはプロシージャの戻り値が格納されます。 CREATE TABLE #temp ステートメントは、result という列を持つ临时表を作成します。 EXEC MyStoredProcedure ステートメントは、プロシージャを実行し、その結果を #temp 表に挿入します。 SELECT @result = result FROM #temp; ステートメントは、#temp 表から result 列の値を @result 変数に格納します。最後に、DROP TABLE #temp; ステートメントは、临时表を削除します。

EXECUTE AS ステートメントを使用して、別のユーザーコンテキストで EXEC プロシージャを実行し、その結果を SQL 変数に格納することができます。

DECLARE @result INT;

EXECUTE AS dbo WITH RESULT SETS (0)
BEGIN
    EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2', @result OUTPUT;
    SELECT @result = @result;
END;

OUTPUT パラメータを使用する 方法は、最も簡単でわかりやすい方法です。ただし、プロシージャが複数の値を返す場合に使用することはできません。
SELECT ステートメントを使用する 方法は、プロシージャが複数の値を返す場合に使用できます。ただし、临时表を作成および削除する必要があるため、少し複雑になります。
EXECUTE AS を使用する 方法は、別のユーザーコンテキストでプロシージャを実行する必要がある場合に使用できます。ただし、最も複雑な方法です。

その他の注意事項

SQL Server 2008 以降では、OUTPUT パラメータの代わりに VALUES パラメータを使用することもできます。
プロシージャの戻り値が NULL の場合、SQL 変数には NULL が割り当てられます。

OUTPUT パラメータを使用する

CREATE PROCEDURE MyStoredProcedure
    @param1 NVARCHAR(50),
    @param2 NVARCHAR(50),
    @result INT OUTPUT
AS
BEGIN
    DECLARE @returnValue INT;

    SET @returnValue = 10;

    SELECT @result = @returnValue;
END;

DECLARE @result INT;

EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2', @result OUTPUT;

SELECT @result;

このコードは、以下のような出力を生成します。

SELECT ステートメントを使用する

CREATE PROCEDURE MyStoredProcedure
    @param1 NVARCHAR(50),
    @param2 NVARCHAR(50)
AS
BEGIN
    DECLARE @returnValue INT;

    SET @returnValue = 20;

    INSERT INTO #temp (result)
    VALUES (@returnValue);
END;

DECLARE @result INT;

CREATE TABLE #temp (result INT);

EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2';

SELECT @result = result FROM #temp;

DROP TABLE #temp;

EXECUTE AS を使用する 方法のサンプルコードは、以下のとおりです。

CREATE PROCEDURE MyStoredProcedure
    @param1 NVARCHAR(50),
    @param2 NVARCHAR(50),
    @result INT OUTPUT
AS
BEGIN
    DECLARE @returnValue INT;

    SET @returnValue = 30;

    SELECT @result = @returnValue;
END;

DECLARE @result INT;

EXECUTE AS dbo WITH RESULT SETS (0)
BEGIN
    EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2', @result OUTPUT;
    SELECT @result = @result;
END;

複数の値を返すプロシージャ のサンプルコードは、以下のとおりです。

CREATE PROCEDURE MyStoredProcedure
    @param1 NVARCHAR(50),
    @param2 NVARCHAR(50),
    @result1 INT OUTPUT,
    @result2 INT OUTPUT
AS
BEGIN
    DECLARE @returnValue1 INT;
    DECLARE @returnValue2 INT;

    SET @returnValue1 = 40;
    SET @returnValue2 = 50;

    SELECT @result1 = @returnValue1, @result2 = @returnValue2;
END;

DECLARE @result1 INT;
DECLARE @result2 INT;

EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2', @result1 OUTPUT, @result2 OUTPUT;

SELECT @result1, @result2;

40
50

これらのサンプルコードは、sql, sql-server, t-sql の各キーワードに関連するプログラミングについて、より深く理解を深めるのに役立つでしょう。

SQL Server で EXEC 結果を SQL 変数に割り当てるその他の方法

sp_executesql プロシージャは、動的に生成された SQL ステートメントを実行するために使用できます。このプロシージャを使用して、EXEC プロシージャの結果を SQL 変数に格納することができます。

DECLARE @result INT;

DECLARE @sql NVARCHAR(4000);

SET @sql = N'EXEC MyStoredProcedure @param1 = ''value1'', @param2 = ''value2'', @result OUTPUT';

EXEC sp_executesql @sql, N'@result INT OUTPUT', @result OUTPUT;

SELECT @result;

この例では、MyStoredProcedure プロシージャは @result という OUTPUT パラメータを持ち、このパラメータにはプロシージャの戻り値が格納されます。 DECLARE @sql ステートメントは、EXEC プロシージャを呼び出す SQL ステートメントを動的に生成します。 EXEC sp_executesql ステートメントは、動的に生成された SQL ステートメントを実行し、その結果を @result 変数に格納します。

OUTPUT パラメータと INSERT INTO ステートメントを組み合わせることで、EXEC プロシージャの結果を SQL 変数に格納することができます。

DECLARE @result INT;

DECLARE @temp TABLE (result INT);

EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2', @result OUTPUT INSERT INTO @temp;

SELECT @result = result FROM @temp;

DROP TABLE @temp;

DECLARE @result INT;

EXECUTE AS dbo WITH RESULT SETS (0)
BEGIN
    EXEC MyStoredProcedure @param1 = 'value1', @param2 = 'value2', @result OUTPUT;
    SELECT @result = @result;
END;