DECLARE @local_variable (Transact-SQL)

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 中的 SQL 终结点 Microsoft Fabric 中的仓库 Microsoft Fabric SQL 数据库

变量是在批处理或过程的主体中用 DECLARE 语句声明的，并用 SET 或 SELECT 语句赋值。 游标变量可使用此语句声明，并可用于其他与游标相关的语句。 除非在声明中提供值，否则声明之后所有变量将初始化为 NULL。

Transact-SQL 语法约定

语法

以下为 SQL Server 和 Azure SQL 数据库的语法：

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、并行数据仓库和 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<>
定义表数据类型。 表声明包括列定义、名称、数据类型和约束。 允许的约束类型只包括 PRIMARY KEY、UNIQUE、NULL 和 CHECK。 如果类型绑定了规则或默认定义，则不能将别名数据类型用作列标量数据类型。

table_type_definition<>

CREATE TABLE 中用于定义表的信息子集。 其中包含了元素和主要定义。 有关详细信息，请参阅 CREATE TABLE (Transact-SQL)。

n
指示可以指定多个变量并对变量赋值的占位符。 声明表变量时，表变量必须是 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

如果在插入过程中未显式提供值，则指定为列提供的值。 DEFAULT 定义可适用于除定义为 timestamp 或带 IDENTITY 属性的列以外的任何列。 删除表时，将删除 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 行），那么在使用 table 变量时应小心谨慎。 请考虑以下替代方法：

• 当行计数可能更大（超过 100 行）时，临时表可能是比表变量更好的解决方案。
• 如果查询联接 table 变量和其他表，则可使用 RECOMPILE 提示，这使优化器会对 table 变量使用正确的基数。
• 在 Azure SQL 数据库中，从 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;

请参阅

• EXECUTE (Transact-SQL)
• 内置函数 (Transact-SQL)
• SELECT (Transact-SQL)
• 表 (Transact-SQL)
• 类型化的 XML 与非类型化的 XML 的比较