DECLARE @local_variable (Transact-SQL)

發行項
11/25/2024

適用於：MICROSOFT網狀架構倉儲中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System （PDW） SQL 分析端點，Microsoft Fabric SQL 資料庫中的網狀架構倉儲Microsoft網狀架構

變數是利用 DECLARE 陳述式宣告在批次或程序的主體中，並利用 SET 或 SELECT 陳述式來指派值。資料指標變數可以是利用這個陳述式來宣告，且可以搭配其他與資料指標相關的陳述式來使用。在宣告之後，所有變數都會初始化成 NULL，除非在宣告中有提供值。

Transact-SQL 語法慣例

Syntax

下列是 SQL Server 和 Azure SQL Database 的語法：

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

<table_type_definition> ::=
    TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ ,...n ] )

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

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( < index_option > [ ,...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<column_index> ::=
    INDEX index_name [ CLUSTERED | NONCLUSTERED ]
    [ WITH ( <index_option> [ ,... n ] ) ]
    [ ON { partition_scheme_name (column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ ,...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ ,...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<table_index> ::=
{
    {
      INDEX index_name  [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
         (column_name [ ASC | DESC ] [ ,... n ] )
    | INDEX index_name CLUSTERED COLUMNSTORE
    | INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ ,... n ] )
    }
    [ WITH ( <index_option> [ ,... n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}

<index_option> ::=
{
  PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
       [ ON PARTITIONS ( { partition_number_expression | <range> }
       [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ] ]
}

下列語法適用於 Azure Synapse Analytics 和 Parallel Data Warehouse 和 Microsoft Fabric：

DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ ,...n ]

引數

@local_variable

變數的名稱。變數名稱的開頭必須是 at (@) 符號。區域變數名稱必須遵循識別碼的規則。

data_type
任何系統提供的通用語言執行平台 (CLR) 使用者定義資料表類型或別名資料類型。變數的資料類型不可以是 text、ntext 或 image。

如需系統資料類型的詳細資訊，請參閱資料類型 (Transact-SQL)。如需有關 CLR 使用者定義型別或別名資料類型的詳細資訊，請參閱 CREATE TYPE (Transact-SQL)。

= value
以內嵌方式指派值給變數。此值可以是常數或運算式，但是它必須符合變數宣告類型，或是必須可隱含轉換成該類型。如需詳細資訊，請參閱運算式 (Transact-SQL)。

@cursor_variable_name

資料指標變數的名稱。資料指標變數名稱的開頭必須是 at (@) 符號，且必須符合識別碼的規則。

CURSOR
指定變數是本機資料指標變數。

@table_variable_name
table 類型的變數名稱。變數名稱的開頭必須是 at (@) 符號，且必須符合識別碼的規則。

<table_type_definition>
定義 table 資料類型。資料表宣告包括資料行定義、名稱、資料類型和條件約束。允許使用的條件約束類型只有 PRIMARY KEY、UNIQUE、NULL 和 CHECK。如果規則或預設定義繫結至別名資料類型，就無法利用別名資料類型來當做資料行純量資料類型。

<table_type_definition>

在 CREATE TABLE 中用來定義資料表的資訊子集。這裡包括元素和必要定義。如需詳細資訊，請參閱 CREATE TABLE (TRANSACT-SQL)。

n
預留位置，表示可以指定多個變數，且可以指派這些變數的值。當宣告 table 變數時，table 變數必須是 DECLARE 陳述式所宣告的唯一變數。

column_name

資料表中資料行的名稱。

scalar_data_type
指定資料行是一種純量資料類型。

computed_column_expression
定義計算資料行值的運算式。它是從運算式中，利用相同資料表中其他資料行計算而得。例如，計算資料行可能會有的定義是：cost AS price * qty。這個運算式可以是非計算的資料行名稱、常數、內建函式、變數，或是上述項目由一或多個運算子所連接的任何組合。運算式不能是子查詢或使用者定義函式。運算式不能參考 CLR 使用者定義型別。

[ COLLATE collation_name ]

指定資料行的定序。 collation_name可以是 Windows 定序名稱或 SQL 定序名稱，而且僅適用於 char、varchar、text、nchar、nvarchar 和 ntext 數據類型的數據行。若未指定，便會將使用者定義資料類型的定序指派給這個資料行 (如果資料行是使用者定義資料類型)，否則，便會指派目前資料庫的定序。

如需有關 Windows 和 SQL 定序名稱的詳細資訊，請參閱 COLLATE (Transact-SQL)。

DEFAULT

指定在插入期間未明確提供值時，提供給資料行的值。除了定義為 timestamp 或含有 IDENTITY 屬性的資料行之外，任何資料行都可以套用 DEFAULT 定義。當卸除資料表時，便會移除 DEFAULT 定義。預設值只能使用常數值 (如字元字串)、系統函數 (如 SYSTEM_USER()) 或 NULL。若要維護與舊版 SQL Server 的相容性，您可以將限制式名稱指派給 DEFAULT。

constant_expression
這是用來當做資料行預設值的常數、NULL 或系統函數。

IDENTITY

指出新資料行是識別欄位。當資料列新增到資料表時，SQL Server 會提供該資料行的唯一累加值。識別欄位通常用來搭配 PRIMARY KEY 限制式一起使用，作為資料表的唯一資料列識別碼。可以將 IDENTITY 屬性指派給 tinyint、smallint、int、decimal(p,0) 或 numeric(p,0) 資料行。每份資料表都只能建立一個識別欄位。繫結的預設值和 DEFAULT 限制式無法搭配識別欄位使用。您必須同時指定種子和遞增，或同時不指定這兩者。如果同時不指定這兩者，預設值便是 (1,1)。

seed
載入資料表的第一個資料列所用的值。

increment
加入先前載入的資料列之識別值的累加值。

ROWGUIDCOL

指出新資料行是一個資料列全域唯一識別碼資料行。每個資料表只能有一個 uniqueidentifier 資料行指定為 ROWGUIDCOL 資料行。 ROWGUIDCOL 屬性只能指派給 uniqueidentifier 資料行。

NULL | NOT NULL

指出變數中是否允許 null。預設值是 NULL。

PRIMARY KEY

利用唯一索引來強制執行一或多個指定資料行之實體完整性的限制式。每份資料表都只能建立一個 PRIMARY KEY 條件約束。

UNIQUE

利用唯一索引來提供一或多個指定資料行之實體完整性的限制式。一份資料表可以有多個 UNIQUE 條件約束。

CLUSTERED | NONCLUSTERED

指出針對 PRIMARY KEY 或 UNIQUE 條件約束建立叢集或非叢集索引。 PRIMARY KEY 條件約束使用 CLUSTERED，UNIQUE 條件約束則使用 NONCLUSTERED。

只能為一個條件約束指定 CLUSTERED。如果針對 UNIQUE 條件約束指定 CLUSTERED，且指定了 PRIMARY KEY 條件約束，則 PRIMARY KEY 會使用 NONCLUSTERED。

CHECK

透過限制可能輸入一或多個資料行的值來強制執行值域完整性的限制式。

logical_expression
傳回 TRUE 或 FALSE 的邏輯運算式。

<index_option>

指定一個或多個索引選項。您無法明確建立 table 變數的索引，也無法保留 table 變數的任何統計資料。從 SQL Server 2014 (12.x) 開始，已引進新的語法，允許您建立特定內嵌資料表定義的索引類型。您可以使用這個新的語法在 table 變數上建立索引，作為資料表定義的一部分。在某些情況下，改用完整索引支援和統計資料的暫存資料表可以提升效能。

如需這些選項的完整描述，請參閱 CREATE TABLE。

資料表變數和資料列估計

Table 變數沒有散發統計資料。在許多情況下，最佳化工具都將以資料表變數具有零個或一個資料列作為假設前提來建立查詢計畫。如需詳細資訊，請檢閱資料表資料類型 - 限制事項。

基於這個原因，若您預期會有較多數目的資料列 (超過 100 列)，就應該謹慎使用資料表變數。考慮使用下列替代方案：

當資料列計數可能會更大 (大於 100) 時，暫存資料表可能是比資料表變數更好的解決方案。
如果查詢聯結資料表變數與其他資料表，則可使用 RECOMPILE 提示，而使最佳化工具針對資料表變數使用正確的基數。
在 Azure SQL Database 中，以及從 SQL Server 2019 (15.x) 開始，資料表變數延後編譯功能會根據實際資料表變數資料列計數傳播基數估計值，以提供更精確的資料列計數來將執行計畫最佳化。如需詳細資訊，請參閱 SQL 資料庫中的智慧型查詢處理。

備註

批次或程序通常會利用變數來當做 WHILE、LOOP 或 IF...ELSE 區塊的計數器。

變數只能用在運算式中，不能用來取代物件名稱或關鍵字。若要建構動態 SQL 陳述式，請使用 EXECUTE。

區域變數的範圍是其宣告所在的批次。

資料表變數不一定會常駐記憶體。在記憶體壓力下，屬於資料表變數的分頁可以推送到 tempdb。

您可以在資料表變數中定義內嵌索引。

下列陳述式可以將目前指派了資料指標的資料指標變數當做一項來源來參考：

CLOSE 陳述式
DEALLOCATE 陳述式
FETCH 陳述式
OPEN 陳述式
定點 DELETE 或 UPDATE 陳述式
SET CURSOR 變數陳述式 (在右側)

在所有的這些陳述式中，如果參考的資料指標變數存在，但目前未向其配置資料指標，SQL Server 便會引發錯誤。如果所參考的資料指標變數不存在，SQL Server 便會產生針對其他類型的未宣告變數所產生的相同錯誤。

資料指標變數：

可以是資料指標類型或另一個資料指標變數的目標。如需詳細資訊，請參閱SET @local_variable (Transact-SQL)。
如果資料指標變數目前未指派任何資料指標，就可以在 EXECUTE 陳述式中，將其當做輸出資料指標參數的目標來參考。
應該視為指向資料指標的指標。

範例

A. 使用 DECLARE

下列範例會利用名稱為 @find 的本機變數來擷取開頭是 Man 的所有姓氏的連絡資訊。

USE AdventureWorks2022;
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 AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;

結果集如下所示。

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,000,000 的 Adventure Works Cycles 銷售代表姓名。

USE AdventureWorks2022;
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 子句所指定的值。之後的兩個 SELECT 陳述式會傳回 @MyTableVar 中的值，以及 Employee 資料表中更新作業的結果。 INSERTED.ModifiedDate 資料行中的結果不同於 Employee 資料表中 ModifiedDate 資料行的值。這是因為將 AFTER UPDATE 值更新成目前日期的 ModifiedDate 觸發程序是定義在 Employee 資料表上。不過，從 OUTPUT 傳回的資料行會反映引發觸發程序之前的資料。如需詳細資訊，請參閱 OUTPUT 子句 (Transact-SQL)。

USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
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.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. 使用內嵌索引宣告資料表類型的變數

下列範例會建立具有叢集內嵌索引和兩個非叢集內嵌索引的 table 變數。

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    PRIMARY KEY CLUSTERED (EmpID),
    UNIQUE NONCLUSTERED (EmpID),
    INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO

下列查詢會傳回上一個查詢中建立之索引的相關資訊。

SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO

E. 宣告使用者定義資料表類型的變數

下列範例會建立資料表值參數或稱為 @LocationTVP 的資料表變數。這需要稱為 LocationTableType 的對應使用者定義資料表類型。如需有關如何建立使用者定義資料表類型的詳細資訊，請參閱 CREATE TYPE (Transact-SQL)。如需資料表值參數的詳細資訊，請參閱使用資料表值參數 (資料庫引擎)。

DECLARE @LocationTVP
AS LocationTableType;

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

F. 使用 DECLARE

下列範例會利用名稱為 @find 的本機變數來擷取開頭是 Walt 的所有姓氏的連絡資訊。

-- Uses AdventureWorks

DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;

G. 使用 DECLARE 與兩個變數

下列範例會擷取使用變數來指定 DimEmployee 資料表中員工的名字和姓氏。

-- Uses AdventureWorks

DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);

SET @lastName = 'Walt%';
SET @firstName = 'Bryan';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;

共用方式為