SET @local_variable (Transact-SQL)

[アーティクル]
12/22/2023

適用対象: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

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

@local_variable

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

property_name

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

field_name

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

udt_name

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

{ . |:: }

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

method_name ( 引数 [ ,... n ] )

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

@SQLCLR_local_variable

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

mutator_method

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

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

複合代入演算子です。

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

式 (expression)

任意の有効な式。

cursor_variable

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

cursor_name

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

CURSOR

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

SCROLL

カーソルで、すべてのフェッチオプション FIRST(、、、LAST、RELATIVENEXTPRIORABSOLUTE) がサポートされることを指定します。指定する場合も指定SCROLLFAST_FORWARDできません。

FORWARD_ONLY

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

STATIC

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

KEYSET

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

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

DYNAMIC

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

FAST_FORWARD

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

READ_ONLY

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

SCROLL LOCKS

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

OPTIMISTIC

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

TYPE_WARNING

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

FOR select_statement

カーソルの結果セットを定義する標準 SELECT ステートメント。キーワード (keyword)FOR BROWSEINTO。カーソル宣言のselect_statement内では使用できません。

をUNION使用DISTINCTするか、GROUP BY集計HAVING式をselect_listに含める場合、カーソルは次のようにSTATIC作成されます。

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

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

READ ONLY

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

UPDATE [ OF column_name [ ,...n ]

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

解説

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

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

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

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

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

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

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

アクセス許可

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

例

この記事には AdventureWorks2022 サンプルデータベースが必要です。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトのホームページからダウンロードできます。

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

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

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

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

次の例では、名前付きの@stateローカル変数を作成し、ステートメント内のローカル変数をSELECT使用して、状態Oregonが < a0/> であるすべての従業員の名 (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. グローバルカーソルで Standard Edition T を使用する

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

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. Standard Edition T を使用してカーソルを定義する

次の例では、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. Standard Edition T を使用して初期化された変数の値を出力する