次の方法で共有

  • [アーティクル]

DECLARE @local_variable (Transact-SQL)

変数は、バッチやプロシージャの中で DECLARE ステートメントを使用して宣言し、SET ステートメントまたは SELECT ステートメントを使用して値を割り当てます。このステートメントでは、他のカーソル関連のステートメントで使用できるカーソル変数を宣言できます。宣言の一部として値を指定しない場合、宣言の後、すべての変数は NULL として初期化されます。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

DECLARE 
     { 
{{ @local_variable [AS] data_type } | [ =value ] }
    | { @cursor_variable_name CURSOR }
} [,...n] 
    | { @table_variable_name [AS] <table_type_definition> | <user-defined table type> } 

<table_type_definition> ::= 
     TABLE ( { <column_definition> | <table_constraint> } [ ,... ] 
   ) <column_definition> ::= 
     column_name { scalar_data_type | AS computed_column_expression }
     [ COLLATE collation_name ] 
     [ [ DEFAULT constant_expression ] | IDENTITY [ (seed ,increment ) ] ] 
     [ ROWGUIDCOL ] 
     [ <column_constraint> ] 

<column_constraint> ::= 
     { [ NULL | NOT NULL ] 
     | [ PRIMARY KEY | UNIQUE ] 
     | CHECK (logical_expression ) 
     | WITH ( < index_option > )
     } 

<table_constraint> ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] ) 
     | CHECK (search_condition ) 
     } 

<index_option> ::=
See CREATE TABLE for index option syntax.

引数

  • @local_variable
    変数名を指定します。変数名は、アット マーク (@) で始める必要があります。ローカル変数名は、識別子の規則に従っている必要があります。

  • data_type
    システム提供の共通言語ランタイム (CLR) ユーザー定義テーブル型または別名データ型を指定します。変数に、text 型、ntext 型、または image 型は指定できません。

    システム データ型の詳細については、「データ型 (Transact-SQL)」を参照してください。CLR ユーザー定義型または別名データ型に関する詳細については、「CREATE TYPE (Transact-SQL)」を参照してください。

  • =value
    インラインで値を変数に代入します。値には定数または式を指定できますが、変数宣言の型と同じであるか、その型に暗黙的に変換できる必要があります。

  • @cursor_variable_name
    カーソル変数の名前を指定します。カーソル変数名はアット マーク (@) で始まり、識別子の規則に従っている必要があります。

  • CURSOR
    変数がローカルなカーソル変数であることを指定します。

  • @table_variable_name
    table 型の変数の名前を指定します。変数名はアット マーク (@) で始まり、識別子の規則に従っている必要があります。

  • <table_type_definition>
    table 型を定義します。テーブルの定義には、列の定義、名前、データ型、および制約が含まれます。指定できる制約の種類は、PRIMARY KEY、UNIQUE、NULL、および CHECK のみです。別名データ型にルールや既定の定義がバインドされている場合、別名データ型を列のスカラー データ型として使用することはできません。

    <table_type_definition> は、CREATE TABLE でテーブルを定義するときに使用する情報のサブセットです。これには要素と必須の定義が含まれます。詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。

  • n
    複数の変数を指定し、値を割り当てることができることを示すプレースホルダーです。table 変数を宣言する場合、1 つの DECLARE ステートメント内に table 変数以外の変数を定義することはできません。

  • column_name
    テーブル内の列名を指定します。

  • scalar_data_type
    列がスカラー型であることを指定します。

  • computed_column_expression
    計算列の値を定義する式を指定します。計算列は、同じテーブルの他の列を使用した式によって計算されます。たとえば、計算列には、cost AS price * qty という定義の式を指定できます。この式には、計算列以外の列の名前、定数、組み込み関数、変数、およびこれらを 1 つ以上の演算子で結合した組み合わせを使用できます。この式はサブクエリまたはユーザー定義関数にはできません。この式は CLR ユーザー定義型を参照できません。

  • [ COLLATE collation_name]
    列の照合順序を指定します。collation_name には、Windows の照合順序名または SQL 照合順序名のいずれかを指定できます。データ型が char、varchar、text、nchar、nvarchar、および ntext の列にのみ適用できます。collation_name を指定しないと、列がユーザー定義データ型である場合はユーザー定義データ型の照合順序、または現在のデータベースの照合順序が列に割り当てられます。

    Windows 照合順序名および SQL 照合順序名の詳細については、「COLLATE (Transact-SQL)」を参照してください。

  • DEFAULT
    挿入の際に明示的な値を指定しない場合に、列に入力される値を指定します。DEFAULT 定義は、timestamp として定義された列または IDENTITY プロパティを持つ列以外のすべての列に適用できます。テーブルが削除されると、DEFAULT 定義も削除されます。既定値として使用できるのは、文字列などの定数値、SYSTEM_USER() などのシステム関数、または NULL だけです。SQL Server の旧バージョンとの互換性を保つため、DEFAULT に制約名を割り当てることができます。

  • constant_expression
    列の既定値として使用される定数、NULL またはシステム関数を指定します。

  • IDENTITY
    新しい列が ID 列であることを指定します。テーブルに行が新しく追加されると、SQL Server では列に一意な増分値が設定されます。ID 列は通常、PRIMARY KEY 制約と組み合わせて使用し、テーブルの一意な行識別子の役割を果たします。IDENTITY プロパティは、tinyint 型、smallint 型、int 型、decimal(p,0) 型、または numeric(p,0) 型の列に割り当てることができます。ID 列は 1 つのテーブルにつき 1 つだけ作成できます。バインドされた既定値および DEFAULT 制約を ID 列と組み合わせて使用することはできません。seed と increment は、両方を指定するか、どちらも指定しないでください。どちらも指定しないときの既定値は (1,1) です。

  • seed
    テーブルに読み込まれる最初の行に使用される値です。

  • increment
    既に読み込まれている前の行の ID 値に加算される増分値です。

  • ROWGUIDCOL
    新しい列が行グローバル一意識別子列であることを指定します。1 つのテーブルにつき、1 つの uniqueidentifier 列だけを ROWGUIDCOL 列に指定できます。ROWGUIDCOL プロパティは uniqueidentifier 列にだけ割り当てることができます。

  • NULL | NOT NULL
    列の中で NULL 値が許可されるかどうかを決定するキーワードです。

  • PRIMARY KEY
    一意なインデックスによって、特定の 1 つ以上の列に対してエンティティの整合性を設定する制約です。PRIMARY KEY 制約は、1 つのテーブルにつき 1 つだけ作成できます。

  • UNIQUE
    一意なインデックスによって、特定の 1 つ以上の列に対してエンティティの整合性を設定する制約です。1 つのテーブルには複数の UNIQUE 制約を指定できます。

  • CHECK
    1 つ以上の列に入力できる値を制限することによってドメインの整合性を設定する制約です。

  • logical_expression
    TRUE または FALSE を返す論理式です。

  • <user-defined table type>
    変数がユーザー定義テーブル型であることを指定します。

説明

変数は、バッチやプロシージャの中で、WHILE、LOOP、または IF...ELSE ブロックのカウンターとして使用される場合があります。

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

ローカル変数のスコープは、その変数が宣言されるバッチです。

現在カーソルが割り当てられているカーソル変数は、次のステートメントの中でソースとして参照できます。

  • CLOSE ステートメント

  • DEALLOCATE ステートメント

  • FETCH ステートメント

  • OPEN ステートメント

  • 位置指定の DELETE または UPDATE ステートメント

  • SET CURSOR 変数ステートメント (右側)

どのステートメントの場合も、参照されるカーソル変数は存在するが、現在カーソルが割り当てられていない場合は、SQL Server でエラーが発生します。参照されているカーソル変数が存在しない場合、SQL Server では、宣言されていない別の型の変数に対して発生するエラーと同じエラーが発生します。

カーソル変数には、次の特徴があります。

  • カーソルの種類または別のカーソル変数の対象になります。詳細については、「SET @local_variable (Transact-SQL)」を参照してください。

  • カーソル変数に現在カーソルが割り当てられていない場合、EXECUTE ステートメント内の出力カーソル パラメーターの対象として参照できます。

  • カーソルへのポインターとして扱われます。カーソル変数の詳細については、「Transact-SQL カーソル」を参照してください。

使用例

A. DECLARE を使用する

次の例では、@find という名前のローカル変数を使って、Man で始まるすべての姓の連絡先情報を取得します。

USE AdventureWorks2008R2;
GO
DECLARE @find varchar(30);
/* Also allowed: 
DECLARE @find varchar(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person p 
JOIN Person.PersonPhone ph
ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE 'Man%';

以下に結果セットを示します。

LastName FirstName Phone

-------------------------------------------------- -------------------------------------------------- -------------------------

Manchepalli Ajay 1 (11) 500 555-0174

Manek Parul 1 (11) 500 555-0146

Manzanares Tomas 1 (11) 500 555-0178

(3 row(s) affected)

B. DECLARE で 2 つの変数を使用する

次の例では、北米販売区域に勤務しており、年間売上高が $2,000,000 以上である Adventure Works Cycles 販売担当者の名前を取得します。

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
GO
DECLARE @Group nvarchar(50), @Sales money;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;

C. table 型の変数を宣言する

次の例では、UPDATE ステートメントの OUTPUT 句で指定される値を格納する table 変数を作成します。この後に、@MyTableVar 内の値、および Employee テーブルの更新操作の結果を返す 2 つの SELECT ステートメントが続きます。INSERTED.ModifiedDate 列の結果が、Employee テーブルの ModifiedDate 列の値と異なることに注意してください。これは、ModifiedDate の値を現在の日付に更新する AFTER UPDATE トリガーが、Employee テーブルで定義されるためです。ただし、OUTPUT から返される列には、トリガーが起動される前のデータが反映されます。詳細については、「OUTPUT 句 (Transact-SQL)」を参照してください。

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.BusinessEntityID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. ユーザー定義テーブル型の変数を宣言する

次の例では、@LocationTVP というテーブル値パラメーターまたはテーブル変数を作成します。これには、LocationTableType という対応するユーザー定義テーブル型が必要です。ユーザー定義テーブル型の作成方法の詳細については、「CREATE TYPE (Transact-SQL)」を参照してください。テーブル値パラメーターの詳細については、「テーブル値パラメーター (データベース エンジン)」を参照してください。

DECLARE @LocationTVP 
AS LocationTableType;