SET @local_variable (Transact-SQL)
適用対象: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Microsoft Fabric のSQL 分析エンドポイント
Microsoft Fabric のウェアハウス
DECLARE @local_variable
ステートメントを使用して以前に作成した、指定したローカル変数を、指定した値に設定します。
構文
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 ( 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_ONLY
READ_ONLY
カーソルを指定します。 FAST_FORWARD
SCROLL
も指定されている場合は指定できません。
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)
この記事の Transact-SQL コード サンプルは AdventureWorks2022
または AdventureWorksDW2022
サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。
J. SET を使用して初期化された変数の値を出力する
次の例では、@myVar
変数を作成し、文字列値を代入して、@myVar
変数の値を出力します。
DECLARE @myVar CHAR(20);
SET @myVar = 'This is a test';
SELECT TOP 1 @myVar FROM sys.databases;
K. SELECT ステートメントで SET を使用して、値が割り当てられたローカル変数を使用する
次の例では、@dept
という名前のローカル変数を作成し、SELECT
ステートメントでこのローカル変数を使用して、Marketing
部門で働くすべての従業員の名 (FirstName
) と家族名 (LastName
) を検索します。
DECLARE @dept CHAR(25);
SET @dept = N'Marketing';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name
FROM DimEmployee
WHERE DepartmentName = @dept;
L. ローカル変数に複合代入を使用する
次の 2 つの例では、同じ結果が生成されます。 どちらも @NewBalance
というローカル変数を作成し、その値に 10
を乗算して、SELECT
ステートメントでローカル変数の新しい値を表示します。 2 番目の例では、複合代入演算子を使用します。
/* Example one */
DECLARE @NewBalance INT;
SET @NewBalance = 10;
SET @NewBalance = @NewBalance * 10;
SELECT TOP 1 @NewBalance
FROM sys.tables;
/* Example Two */
DECLARE @NewBalance INT = 10;
SET @NewBalance *= 10;
SELECT TOP 1 @NewBalance
FROM sys.tables;
M. クエリから値を割り当てる
次の例では、クエリを使用して変数に値を代入します。
-- Uses AdventureWorks
DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM dbo.DimCustomer);
SELECT TOP 1 @rows FROM sys.tables;