在 Synapse SQL 池中分配变量Assign variables in Synapse SQL pool

本文介绍在 SQL 池中分配 T-SQL 变量的基本技巧。In this article, you'll find essential tips for assigning T-SQL variables in SQL pool.

使用 DECLARE 设置变量Set variables with DECLARE

使用 DECLARE 语句或 SET 语句设置 SQL 池中的变量。Variables in SQL pool are set using the DECLARE statement or the SET statement. 使用 DECLARE 初始化变量是在 SQL 池中设置变量值的最灵活方式之一。Initializing variables with DECLARE is one of the most flexible ways to set a variable value in SQL pool.

DECLARE @v  int = 0
;

还可以使用 DECLARE 一次性设置多个变量。You can also use DECLARE to set more than one variable at a time. 不能使用 SELECT 或 UPDATE 执行以下操作:You can't use SELECT or UPDATE to do the following:

DECLARE @v  INT = (SELECT TOP 1 c_customer_sk FROM Customer where c_last_name = 'Smith')
,       @v1 INT = (SELECT TOP 1 c_customer_sk FROM Customer where c_last_name = 'Jones')
;

不能在同一 DECLARE 语句中初始化和使用变量。You can't initialize and use a variable in the same DECLARE statement. 为了演示要点,允许出现以下示例中的情况,因为同一 DECLARE 语句中初始化和使用了 @p1。To illustrate the point, the following example is not allowed since @p1 is both initialized and used in the same DECLARE statement. 因此,以下示例给出了一个错误:As such, the following example gives an error:

DECLARE @p1 int = 0
,       @p2 int = (SELECT COUNT (*) FROM sys.types where is_user_defined = @p1 )
;

使用 SET 来设置值Set values with SET

SET 是设置单个变量的常见方法。SET is a common method for setting a single variable.

以下语句是使用 SET 设置变量的有效方法:The following statements are all valid ways to set a variable with SET:

SET     @v = (Select max(database_id) from sys.databases);
SET     @v = 1;
SET     @v = @v+1;
SET     @v +=1;

一次只能使用 SET 设置一个变量。You can only set one variable at a time with SET. 但是可使用复合运算符。However, compound operators are permissible.

限制Limitations

不能使用 UPDATE 来分配变量。You can't use UPDATE for variable assignment.

后续步骤Next steps

有关更多开发技巧,请参阅 开发概述For more development tips, see development overview.