SET @local_variable (Transact-SQL)

[アーティクル]
07/05/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric の SQL 分析エンドポイント Microsoft Fabric のウェアハウス

DECLARE @local_variable ステートメントを使用して以前に作成した、指定したローカル変数を、指定した値に設定します。

Transact-SQL 構文表記規則

構文

SQL Server、Azure SQL Database、および Azure SQL Managed Instance の構文:

SET
{ @local_variable
    [ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }
}
| { @SQLCLR_local_variable.mutator_method }
| { @local_variable
    { += | -= | *= | /= | %= | &= | ^= | |= } expression
}
| { @cursor_variable =
    { @cursor_variable | cursor_name
    | { CURSOR [ [ LOCAL | GLOBAL ] ]
        [ FORWARD_ONLY | SCROLL ]
        [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
        [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
        [ TYPE_WARNING ]
    FOR select_statement
        [ FOR { READ ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
      }
    }
}

Azure Synapse Analytics および Parallel Data Warehouse および Microsoft Fabric の構文:

SET @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression

引数

@local_variable

cursor、text、ntext、image、または table を除く任意の型の変数の名前。変数名の先頭には 1 つのアットマーク ( @ ) を指定します。変数名は識別子の規則に従っている必要があります。

property_name

ユーザー定義型のプロパティ。

field_name

ユーザー定義型のパブリックフィールド。

udt_name

共通言語ランタイム (CLR) ユーザー定義型の名前。

{ . |:: }

CLR ユーザー定義型のメソッドを指定します。インスタンス (非静的) メソッドの場合は、ピリオド (.) を使用します。静的メソッドの場合は、2 つのコロン (::) を使用します。 CLR ユーザー定義型のメソッド、プロパティ、またはフィールドを呼び出すには、その型に対する EXECUTE 権限が必要です。

method_name ( argument [ ,... n ] )

ユーザー定義型のメソッド。このメソッドでは、ある型のインスタンスの状態を変更するために、1 つ以上の引数を受け取ります。静的メソッドはパブリックであることが必要です。

@SQLCLR_local_variable

型がアセンブリに存在する変数。詳細については、 Common 言語ランタイム (CLR) 統合プログラミングの概念を参照してください。

mutator_method

オブジェクトの状態を変更できるアセンブリのメソッド。このメソッドには SQLMethodAttribute.IsMutator が適用されます。

{ += | -= | *= | /= | %= | &= | ^= | |= }

複合代入演算子です。

+= - 追加と割り当て
-= - 減算と割り当て
*= - 乗算と代入
/= - 除算と割り当て
%= - 剰余と割り当て
&= - ビットごとの AND と割り当て
^= - ビットごとの XOR と割り当て
|= - ビットごとの OR と割り当て

式 (expression)

任意の有効な式。

cursor_variable

カーソル変数の名前を指定します。ターゲットのカーソル変数が以前に他のカーソルを参照していた場合は、以前の参照は削除されます。

cursor_name

DECLARE CURSOR ステートメントを使用して宣言されたカーソルの名前。

CURSOR

SET ステートメントにカーソルの宣言が含まれていることを指定します。

SCROLL

カーソルがすべてのフェッチオプション ( FIRST、 LAST、 NEXT、 PRIOR、 RELATIVE、および ABSOLUTE) をサポートすることを指定します。 FAST_FORWARDも指定する場合、SCROLLを指定することはできません。

FORWARD_ONLY

カーソルが FETCH NEXT オプションのみをサポートすることを指定します。カーソルは、最初の行から最後の行への一方向にのみ取得されます。 STATIC、KEYSET、またはDYNAMICキーワードを指定せずにFORWARD_ONLYを指定すると、カーソルはDYNAMICとして実装されます。 FORWARD_ONLYまたはSCROLLを指定しない場合は、キーワードSTATIC、KEYSET、またはDYNAMICを指定しない限り、FORWARD_ONLYが既定値になります。 STATIC、KEYSET、およびDYNAMICカーソルの場合、SCROLLが既定です。

STATIC

データの一時コピーを作成するためのカーソルを定義します。作成されるコピーは、カーソルで使用されます。カーソルに対するすべての要求は、 tempdbのこの一時テーブルから応答されます。その結果、カーソルが開かれた後でベーステーブルに対して行われた変更は、カーソルに対して行われるフェッチによって返されるデータには反映されません。また、このカーソルでは変更はサポートされていません。

KEYSET

カーソルを開くときに、カーソル内の行の構成要素と順序が固定されることを指定します。行を一意に識別するキーのセットは、 tempdbの keysettable に組み込まれます。ベーステーブル内にあるキー以外の値に対する変更が、カーソル所有者によって実行されるか、または他のユーザーによってコミットされると、その変更は、カーソル所有者がカーソルの周囲をスクロールするときに表示されます。他のユーザーによって行われた挿入は表示されません。Transact-SQL サーバーのカーソルを使用して、挿入を行うことはできません。

行が削除された場合、行をフェッチしようとすると、-2の@@FETCH_STATUSが返されます。カーソル外部からキー値を更新するのは、古い行を削除した後で新しい行を挿入するのと同じです。新しい値を持つ行は表示されず、古い値を持つ行をフェッチしようとすると、-2の@@FETCH_STATUSが返されます。 WHERE CURRENT OF句を指定してカーソルを介して更新が行われる場合は、新しい値が表示されます。

DYNAMIC

結果セット内の行に対して行ったすべてのデータ変更を反映するカーソルを定義します。このデータ変更は、カーソル所有者がカーソルの周囲をスクロールするときに行われたものです。行のデータ値、順序、メンバーシップは、各フェッチ操作で変化する可能性があります。動的カーソルでは、絶対フェッチオプションと相対フェッチオプションはサポートされません。

FAST_FORWARD

最適化を有効にしたFORWARD_ONLYREAD_ONLYカーソルを指定します。 FAST_FORWARDSCROLLも指定されている場合は指定できません。

READ_ONLY

このカーソルによる更新を禁止します。 UPDATEまたはDELETEステートメントのWHERE CURRENT OF句でカーソルを参照することはできません。このオプションは、更新対象のカーソルの既定の機能をオーバーライドします。

SCROLL LOCKS

カーソルによって行われる位置指定更新または位置指定削除の成功が保証されることを指定します。 SQL Server はカーソルに読み取られた行をロックし、後で変更できることを保証します。 FAST_FORWARDも指定されている場合、SCROLL_LOCKSを指定することはできません。

OPTIMISTIC

行がカーソルに読み取られてから更新された場合に、カーソルによって行われる位置指定更新または位置指定削除が失敗することを指定します。 SQL Server では、行がカーソルに読み取られるとき、その行はロックされません。代わりに timestamp 列の値を比較するか、テーブルに timestamp 列がない場合はチェックサム値を使用して、行がカーソルに読み込まれてから変更されたかどうかが判別されます。行が変更されている場合、位置指定更新または位置指定削除の試行は失敗します。 FAST_FORWARDも指定されている場合、OPTIMISTICを指定することはできません。

TYPE_WARNING

カーソルの種類が、要求されたものから別のものに暗黙的に変換された場合、クライアントに警告メッセージが送信されることを指定します。

FOR select_statement

カーソルの結果セットを定義する標準の SELECT ステートメント。キーワード FOR BROWSE、および INTO は、カーソル宣言の select_statement 内では許可されません。

DISTINCT、UNION、GROUP BY、またはHAVINGを使用する場合、またはselect_listに集計式を含める場合、カーソルはSTATICとして作成されます。

基になる各テーブルに一意のインデックスと ISO SCROLL カーソルがない場合、または Transact-SQL KEYSET カーソルが要求された場合、カーソルは自動的に STATIC カーソルになります。

列が一意の行識別子ではないORDER BY句select_statement含まれている場合、DYNAMIC カーソルはKEYSET カーソルに変換され、KEYSET カーソルを開くことができない場合はSTATIC カーソルに変換されます。このプロセスは、ISO 構文を使用して定義されたカーソルに対しても発生しますが、 STATIC キーワードは使用しません。

READ ONLY

このカーソルによる更新を禁止します。 UPDATEまたはDELETEステートメントのWHERE CURRENT OF句でカーソルを参照することはできません。このオプションは、更新対象のカーソルの既定の機能をオーバーライドします。このキーワードは、READとONLYの間にアンダースコアの代わりにスペースを使用することで、以前のREAD_ONLYとは異なります。

UPDATE [ OF column_name [ ,...n ] ]

カーソル内で更新できる列を定義します。 OF <column_name> [ , ...n ] を指定した場合は、指定した列に対してのみ更新できます。リストが指定されていない場合は、カーソルが READ_ONLYとして定義されていない限り、すべての列を更新できます。

解説

変数が宣言されると、 NULLに初期化されます。 SET ステートメントを使用して、宣言された変数にNULLされていない値を割り当てます。変数に値を割り当てる SET ステートメントは、1 つの値を返します。複数の変数を初期化する場合は、ローカル変数ごとに個別の SET ステートメントを使用します。

変数は式の内部だけで使用でき、オブジェクト名やキーワードの代わりに使用することはできません。動的 Transact-SQL ステートメントを作成するには、 EXECUTEを使用します。

SET @cursor_variableの構文規則にはLOCALキーワードとGLOBALキーワードが含まれますが、SET @cursor_variable = CURSOR...構文を使用すると、既定のローカルカーソルデータベースオプションの設定に応じて、カーソルがGLOBALまたはLOCALとして作成されます。

カーソル変数は、グローバルカーソルを参照する場合でも、常にローカルです。カーソル変数でグローバルカーソルを参照する場合、カーソルに対してグローバルカーソル参照とローカルカーソル参照の両方が行われます。詳細については、「 Example D、グローバルカーソルで SET を使用するを参照してください。

詳細については、「DECLARE CURSOR (Transact-SQL)」を参照してください。

複合代入演算子は、変数を含む演算子の右側に式を持つ代入があり、UPDATE、SELECT、およびRECEIVEステートメントのSETがある任意の場所で使用できます。

SELECT ステートメント内の変数を使用して値を連結しないでください (つまり、集計値を計算するため)。 SELECT リスト内のすべての式 (割り当てを含む) が出力行ごとに必ずしも 1 回だけ実行されるとは限らないため、予期しないクエリ結果が発生する可能性があります。詳細については、 KB 287515を参照してください。

アクセス許可

ロール public のメンバーシップが必要です。すべてのユーザーが SET @local_variableを使用できます。

例

この記事の Transact-SQL コードサンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプルデータベースを使用します。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトのホームページからダウンロードできます。

A. SET を使用して初期化された変数の値を出力する

次の例では、@myVar 変数を作成し、文字列値を代入して、@myVar 変数の値を出力します。

DECLARE @myVar CHAR(20);
SET @myVar = 'This is a test';
SELECT @myVar;
GO

B. SELECT ステートメントで SET を使用して、値が割り当てられたローカル変数を使用する

次の例では、@stateという名前のローカル変数を作成し、SELECT ステートメントでローカル変数を使用して、Oregonの状態に住むすべての従業員の名 (FirstName) と家族名 (LastName) を検索します。

USE AdventureWorks2022;
GO
DECLARE @state CHAR(25);
SET @state = N'Oregon';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City
FROM HumanResources.vEmployee
WHERE StateProvinceName = @state;
GO

C: ローカル変数に複合代入を使用する

次の 2 つの例では、同じ結果が生成されます。各例では、 @NewBalanceという名前のローカル変数を作成し、それを 10乗算し、ローカル変数の新しい値を SELECT ステートメントに表示します。 2 番目の例では、複合代入演算子を使用します。

/* Example one */
DECLARE @NewBalance INT;
SET @NewBalance = 10;
SET @NewBalance = @NewBalance * 10;
SELECT @NewBalance;
GO

/* Example Two */
DECLARE @NewBalance INT = 10;
SET @NewBalance *= 10;
SELECT @NewBalance;
GO

D. グローバルカーソルで SET を使用する

次の例では、ローカル変数を作成した後、カーソル変数をグローバルカーソル名に設定します。

DECLARE my_cursor CURSOR GLOBAL
FOR SELECT * FROM Purchasing.ShipMethod
DECLARE @my_variable CURSOR ;
SET @my_variable = my_cursor ;
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable
--(@my_variable) set to the my_cursor cursor.

DEALLOCATE my_cursor;
GO
--There is now only a LOCAL variable reference
--(@my_variable) to the my_cursor cursor.

E. SET を使用してカーソルを定義する

次の例では、SET ステートメントを使用してカーソルを定義します。

DECLARE @CursorVar CURSOR;

SET @CursorVar = CURSOR SCROLL DYNAMIC
FOR
SELECT LastName, FirstName
FROM AdventureWorks2022.HumanResources.vEmployee
WHERE LastName like 'B%';

OPEN @CursorVar;

FETCH NEXT FROM @CursorVar;
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM @CursorVar
END;

CLOSE @CursorVar;
DEALLOCATE @CursorVar;
GO

F. クエリから値を割り当てる

次の例では、クエリを使用して変数に値を代入します。

USE AdventureWorks2022;
GO
DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);
SELECT @rows;
GO

G. 型のプロパティを変更して、ユーザー定義型変数に値を割り当てる

次の例では、ユーザー定義型 (UDT) Point の X プロパティの値を変更することで、その型の値が設定されます。

DECLARE @p Point;
SET @p.X = @p.X + 1.1;
SELECT @p;
GO

この例で参照されている Point UDT の作成に関する詳細と、「ユーザー定義型の作成」の記事の次の例を参照してください。

H. 型のメソッドを呼び出して、ユーザー定義型変数に値を割り当てる

次の例では、ユーザー定義型の point に対して SetXY メソッドを起動し、値を設定します。

DECLARE @p Point;
SET @p=point.SetXY(23.5, 23.5);

I. CLR 型の変数を作成し、ミューテーターメソッドを呼び出す

次の例では、Point 型の変数を作成し、Point のミューテーターメソッドを実行します。

CREATE ASSEMBLY mytest FROM 'c:\test.dll' WITH PERMISSION_SET = SAFE
CREATE TYPE Point EXTERNAL NAME mytest.Point
GO
DECLARE @p Point = CONVERT(Point, '')
SET @p.SetXY(22, 23);

例: Azure Synapse Analytics、Analytics Platform System (PDW)

J. SET を使用して初期化された変数の値を出力する