DECLARE @local_variable (Transact-SQL)

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics 分析平台系统 (PDW) Microsoft Fabric 中的 SQL 分析端点 Microsoft Fabric 中的仓库

变量是在批处理或过程的主体中用 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 的比较