[解決済み] スカラー変数を宣言する必要があります。

質問

ストアドプロシージャにこのSQLを書きましたが、うまくいきません。

declare @tableName varchar(max) = 'TblTest'
declare @col1Name varchar(max) = 'VALUE1'
declare @col2Name varchar(max) = 'VALUE2'
declare @value1 varchar(max)
declare @value2 varchar(200)

execute('Select TOP 1 @value1='+@col1Name+', @value2='+@col2Name+' From '+ @tableName +' Where ID = 61')

select @value1

execute('Select TOP 1 @value1=VALUE1, @value2=VALUE2 From TblTest Where ID = 61')

このSQLは、このエラーを投げます。

スカラー変数 "@value1"を宣言する必要があります。

SQLを動的に生成しているのですが、変数に値を取り込みたいのですが。どうすればいいでしょうか？

どのように解決するのですか？

が表示される原因は DECLARE のエラーは、動的なステートメントが別々のバッチで処理されるためで、これはスコープの問題に帰結します。 SQL Serverで利用できるスコープについては、もっと正式な定義があるのかもしれませんが、一般的には、可用性の高いものから順に、次の3つを覚えておけば十分だと思います。

グローバル :

サーバー全体で利用可能なオブジェクトで、ハッシュ/パウンド記号のダブルで作成される一時テーブルなど( ##GLOBALTABLE のように呼びます）。 グローバルオブジェクトについては、SQL Serverであろうとなかろうと、他のアプリケーションと同じように非常に注意深くなければなりません。 私が本質的に言っているのは、このスコープに入らないようにするための注意点として、特に心に留めておいてほしいということです。

IF ( OBJECT_ID( 'tempdb.dbo.##GlobalTable' ) IS NULL )
BEGIN
    CREATE TABLE ##GlobalTable
    (
        Val             BIT
    );

    INSERT INTO ##GlobalTable ( Val )
    VALUES ( 1 );
END;
GO

-- This table may now be accessed by any connection in any database,
-- assuming the caller has sufficient privileges to do so, of course.

セッション :

特定のspidに参照ロックされたオブジェクト。 私の頭の中では、セッションオブジェクトの唯一のタイプは、#Tableのように定義された通常の一時テーブルです。 セッションスコープにあるということは、本質的には、バッチ ( GO が完了すると、このオブジェクトへの参照は正常に解決され続ける。 これらは は 他のセッションから技術的にアクセス可能 しかし、tempdbでランダムな名前を取得し、それらにアクセスすることは、いずれにせよ少し面倒なので、プログラムでそうすることは、多少偉業でしょう。

-- Start of session;
-- Start of batch;
IF ( OBJECT_ID( 'tempdb.dbo.#t_Test' ) IS NULL )
BEGIN
    CREATE TABLE #t_Test
    (
        Val     BIT
    );

    INSERT INTO #t_Test ( Val )
    VALUES ( 1 );
END;
GO 
-- End of batch;

-- Start of batch;
SELECT  *
FROM    #t_Test;
GO
-- End of batch;

新しいセッション（別の spid を持つ接続）を開くと、上記の 2 番目のバッチは、そのセッションが #t_Test オブジェクト名です。

バッチ :

通常の変数、例えばあなたの @value1 と @value2 は、それらが宣言されたバッチに対してのみスコープされます。 とは異なり #Temp テーブルの場合、クエリブロックが GO これらの変数は、セッションで使用できなくなります。 これが、今回のエラーの原因となったスコープレベルです。

-- Start of session;
-- Start of batch;
DECLARE @test   BIT = 1;

PRINT @test;
GO
-- End of batch;

-- Start of batch;
PRINT @Test;  -- Msg 137, Level 15, State 2, Line 2
              -- Must declare the scalar variable "@Test".
GO
-- End of batch;

なるほど、それで？

このダイナミックステートメントで何が起こっているかというと EXECUTE() コマンドは、実行したバッチを壊すことなく、事実上、別のバッチとして評価されます。 EXECUTE() は良いものですが、しかし sp_executesql() 私は前者を最も単純な場合にのみ使用します（明示的に、ステートメントにほとんど動的要素がない場合、主に、そうでない場合は使い勝手の悪いDDLを騙すために使用します）。 CREATE ステートメントを他のバッチの途中で実行させることができます）。 AaronBertrandの 上記の回答は、動的なステートメントを評価する際にオプティマイザの機能を活用したもので、パフォーマンスも以下と同様となりますが、さらに発展させる価値があるのではないかと考えました。 @param というのは、まあパラメータですね。

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'TblTest'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.TblTest;
    CREATE TABLE dbo.TblTest
    (
        ID      INTEGER,
        VALUE1  VARCHAR( 1 ),
        VALUE2  VARCHAR( 1 )
    );

    INSERT INTO dbo.TblTest ( ID, VALUE1, VALUE2 )
    VALUES ( 61, 'A', 'B' );
END;

SET NOCOUNT ON;

DECLARE @SQL    NVARCHAR( MAX ),
        @PRM    NVARCHAR( MAX ),
        @value1 VARCHAR( MAX ),
        @value2 VARCHAR( 200 ),
        @Table  VARCHAR( 32 ),
        @ID     INTEGER;

    SET @Table = 'TblTest';
    SET @ID = 61;

    SET @PRM = '
        @_ID        INTEGER,
        @_value1    VARCHAR( MAX ) OUT,
        @_value2    VARCHAR( 200 ) OUT';
    SET @SQL = '
        SELECT  @_value1 = VALUE1,
                @_value2 = VALUE2
        FROM    dbo.[' + REPLACE( @Table, '''', '' ) + ']
        WHERE   ID = @_ID;';

EXECUTE dbo.sp_executesql @statement = @SQL, @param = @PRM,
            @_ID = @ID, @_value1 = @value1 OUT, @_value2 = @value2 OUT;

PRINT @value1 + ' ' + @value2;

SET NOCOUNT OFF;