SET @local_variable (Transact-SQL)SET @local_variable (Transact-SQL)

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse

將先前利用 DECLARE @local_variable 陳述式來建立的指定區域變數設為指定的值。Sets the specified local variable, previously created by using the DECLARE @local_variable statement, to the specified value.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

SQL Server 和 Azure SQL Database 的語法:Syntax for SQL Server and Azure SQL Database:

SET   
{ @local_variable  
    [ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }  
}  
|  
{ @SQLCLR_local_variable.mutator_method  
}  
|  
{ @local_variable  
    {+= | -= | *= | /= | %= | &= | ^= | |= } expression  
}  
|   
  { @cursor_variable =   
    { @cursor_variable | cursor_name   
    | { CURSOR [ FORWARD_ONLY | SCROLL ]   
        [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
        [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
        [ TYPE_WARNING ]   
    FOR select_statement   
        [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]   
      }   
    }  
}   

Azure SQL 資料倉儲和平行處理資料倉儲的語法:Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse:

SET @local_variable {+= | -= | *= | /= | %= | &= | ^= | |= } expression  

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

引數Arguments

@ local_variable@ local_variable
除了 cursortextntextimagetable 以外的任何類型變數名稱。The name of a variable of any type except cursor, text, ntext, image, or table. 變數名稱的開頭必須是 at 記號 ( @ )。Variable names must start with one at sign (@). 變數名稱必須遵照識別碼的規則。Variable names must follow the rules for identifiers.

property_nameproperty_name
使用者定義型別的屬性。A property of a user-defined type.

field_namefield_name
使用者定義型別的公用欄位。A public field of a user-defined type.

udt_nameudt_name
Common Language Runtime (CLR) 使用者定義型別的名稱。The name of a common language runtime (CLR) user-defined type.

{ . | :: }
指定 CLR 使用者自訂類型的方法。Specifies a method of a CLR user-define type. 對於執行個體 (非靜態) 方法,請使用句點 ( . )。For an instance (non-static) method, use a period (.). 對於靜態方法,請使用兩個冒號 ( :: )。For a static method, use two colons (::). 若要叫用 CLR 使用者自訂類型的方法、屬性或欄位,您必須具有類型的 EXECUTE 權限。To invoke a method, property, or field of a CLR user-defined type, you must have EXECUTE permission on the type.

method_name ( argument [ , ... n ] )method_name ( argument [ ,... n ] )
利用一個或多個引數來修改類型執行個體狀態的使用者定義型別方法。A method of a user-defined type that takes one or more arguments to modify the state of an instance of a type. 靜態方法必須是公用的。Static methods must be public.

@ SQLCLR_local_variable@ SQLCLR_local_variable
類型位於組件中的變數。A variable whose type is located in an assembly. 如需詳細資訊,請參閱 Common Language Runtime (CLR) 整合程式設計概念For more information, see Common Language Runtime (CLR) Integration Programming Concepts.

mutator_methodmutator_method
組件中可以變更物件狀態的方法。A method in the assembly that can change the state of the object. SQLMethodAttribute.IsMutator 會套用到這個方法。SQLMethodAttribute.IsMutator is applied to this method.

{ += | -= | *= | /= | %= | &= | ^= | |= }
複合指派運算子:Compound assignment operator:

+= 加並指派+= Add and assign

-= 減並指派-= Subtract and assign

*= 乘並指派*= Multiply and assign

/= 除並指派/= Divide and assign

%= 取餘數並指派%= Modulo and assign

&= 位元 AND 並指派&= Bitwise AND and assign

^= 位元 XOR 並指派^= Bitwise XOR and assign

|= 位元 OR 並指派|= Bitwise OR and assign

expressionexpression
任何有效的運算式Any valid expression.

cursor_variablecursor_variable
資料指標變數的名稱。The name of a cursor variable. 如果目標資料指標先前參考不同的資料指標,就會移除先前的參考。If the target cursor variable previously referenced a different cursor, that previous reference is removed.

cursor_namecursor_name
使用 DECLARE CURSOR 陳述式來宣告的資料指標名稱。The name of a cursor declared by using the DECLARE CURSOR statement.

CURSORCURSOR
指定 SET 陳述式包含資料指標的宣告。Specifies that the SET statement contains a declaration of a cursor.

SCROLLSCROLL
指定資料指標支援所有提取選項:FIRST、LAST、NEXT、PRIOR、RELATIVE 和 ABSOLUTE。Specifies that the cursor supports all fetch options: FIRST, LAST, NEXT, PRIOR, RELATIVE, and ABSOLUTE. 當您也指定了 FAST_FORWARD 時,便不能指定 SCROLL。You can't specify SCROLL when you've also specified FAST_FORWARD.

FORWARD_ONLYFORWARD_ONLY
指定資料指標只支援 FETCH NEXT 選項。Specifies that the cursor supports only the FETCH NEXT option. 您只能依單一方向,從第一個到最後一個資料列擷取資料指標。The cursor is retrieved only in one direction, from the first to the last row. 當您指定不含 STATIC、KEYSET 或 DYNAMIC 關鍵字的 FORWARD_ONLY 時,會將資料指標實作成 DYNAMIC。When you specify FORWARD_ONLY without the STATIC, KEYSET, or DYNAMIC keywords, the cursor is implemented as DYNAMIC. 如果您沒有指定 FORWARD_ONLY 或 SCROLL,除非指定了 STATIC、KEYSET 或 DYNAMIC 關鍵字,否則,預設值是 FORWARD_ONLY。If you don't specify either FORWARD_ONLY or SCROLL, FORWARD_ONLY is the default, unless you specify the keywords STATIC, KEYSET, or DYNAMIC. 如果是 STATIC、KEYSET 和 DYNAMIC 資料指標,預設值便是 SCROLL。For STATIC, KEYSET, and DYNAMIC cursors, SCROLL is the default.

STATICSTATIC
定義一個資料指標,它會建立資料暫存複本供資料指標本身使用。Defines a cursor that makes a temporary copy of the data to be used by the cursor. 對於資料指標的所有要求都會透過 tempdb 中的這個暫存資料表來回答。All requests to the cursor are answered from this temporary table in tempdb. 因此,資料指標開啟後對基底資料表所做的修改,不會反映在資料指標提取所傳回的資料中。As a result, modifications made to the base tables after the cursor is opened aren't reflected in the data returned by fetches made to the cursor. 而且,這個資料指標不支援修改。And, this cursor doesn't support modifications.

KEYSETKEYSET
指定在開啟資料指標時,修正資料指標中之資料列的成員資格和順序。Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. 可唯一識別資料列的索引鍵組內建在 tempdb 的 keysettable 中。The set of keys that uniquely identify the rows is built into the keysettable in tempdb. 當資料指標擁有者捲動資料指標時,基底資料表中非索引鍵值的變更 (不論是資料指標擁有者所做的變更還是其他使用者所認可的變更) 都是可見的。Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the cursor owner scrolls around the cursor. 其他使用者的插入便不可見,且無法透過 Transact-SQLTransact-SQL 伺服器資料指標來插入。Inserts made by other users aren't visible, and inserts can't be made through a Transact-SQLTransact-SQL server cursor.

如果刪除某個資料列,嘗試擷取該資料列的動作會傳回值為 -2 的 @@FETCH_STATUS。If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. 從資料指標之外更新索引鍵值,類似於先刪除舊資料列,再插入新資料列。Updates of key values from outside the cursor are similar to a delete of the old row followed by an insert of the new row. 含有新值的資料列不可見,且試圖擷取含有舊值的資料列會傳回值為 -2 的 @@FETCH_STATUS。The row with the new values isn't visible, and tries to fetch the row with the old values return an @@FETCH_STATUS of -2. 如果更新是藉由指定 WHERE CURRENT OF 子句透過資料指標進行,便可看到新值。The new values are visible if the update happens through the cursor by specifying the WHERE CURRENT OF clause.

DYNAMICDYNAMIC
定義一個資料指標,使資料指標擁有者捲動資料指標時,資料指標能夠反映結果集資料列的所有資料變更。Defines a cursor that reflects all data changes made to the rows in its result set as the cursor owner scrolls around the cursor. 每次提取時,資料列的資料值、順序和成員資格都有可能改變。The data values, order, and membership of the rows can change on each fetch. 動態資料指標不支援絕對和相對提取選項。The absolute and relative fetch options aren't supported with dynamic cursors.

FAST_FORWARDFAST_FORWARD
指定啟用了最佳化的 FORWARD_ONLY、READ_ONLY 資料指標。Specifies a FORWARD_ONLY, READ_ONLY cursor with optimizations enabled. 當您也指定了 SCROLL 時,便不能指定 FAST_FORWARD。FAST_FORWARD can't be specified when SCROLL is also specified.

READ_ONLYREAD_ONLY
防止利用這個資料指標來更新。Prevents updates from being made through this cursor. UPDATE 或 DELETE 陳述式中的 WHERE CURRENT OF 子句無法參考這個資料指標。The cursor can't be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. 這個選項會覆寫要更新之資料指標的預設功能。This option overrides the default capability of a cursor to be updated.

SCROLL LOCKSSCROLL LOCKS
指定藉由資料指標進行的定位更新或刪除一定會成功。Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. 當資料列讀入資料指標時,SQL ServerSQL Server 會鎖定這些資料列,以確保之後可對它們進行修改。SQL ServerSQL Server locks the rows as they're read into the cursor to guarantee their availability for later modifications. 當您也指定了 FAST_FORWARD 時,便不能指定 SCROLL_LOCKS。You can't specify SCROLL_LOCKS when FAST_FORWARD is also specified.

OPTIMISTICOPTIMISTIC
指定如果將資料列讀入資料指標之後,又更新了這些資料列,則透過資料指標來進行的定位更新或刪除不會成功。Specifies that positioned updates or deletes made through the cursor don't succeed if the row was updated since being read into the cursor. 當資料列讀入資料指標時,SQL ServerSQL Server 不會鎖定資料列。SQL ServerSQL Server doesn't lock rows as they're read into the cursor. 相反地,它會利用 timestamp 資料行值的比較 (如果資料表沒有 timestamp 資料行,便會利用總和檢查碼值) 來判斷資料列讀入資料指標之後,是否會修改資料列。Instead, it uses comparisons of timestamp column values, or a checksum value, if the table has no timestamp column, to determine if the row was modified after being read into the cursor. 如果修改了資料列,試圖執行的定位更新或刪除便會失敗。If the row was modified, the attempted positioned update or delete fails. 當您也指定了 FAST_FORWARD 時,便不能指定 OPTIMISTIC。You can't specify OPTIMISTIC when FAST_FORWARD is also specified.

TYPE_WARNINGTYPE_WARNING
指定當資料指標從要求的類型隱含地轉換成另一個類型時,便傳送一則警告訊息給用戶端。Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another.

FOR select_statementFOR select_statement
這是定義資料指標結果集的標準 SELECT 陳述式。Is a standard SELECT statement that defines the result set of the cursor. 資料指標宣告的 select_statement 內不允許有 FOR BROWSE 和 INTO 關鍵字。The keywords FOR BROWSE, and INTO aren't allowed within the select_statement of a cursor declaration.

如果您使用 DISTINCT、UNION、GROUP BY 或 HAVING,或在 select_list 中包含彙總運算式,就會將資料指標建立成 STATIC。If you use DISTINCT, UNION, GROUP BY, or HAVING, or you include an aggregate expression in the select_list, the cursor is created as STATIC.

如果每個基礎資料表都沒有唯一索引,且要求 ISO SCROLL 資料指標或 Transact-SQLTransact-SQL KEYSET 資料指標,它會自動成為 STATIC 資料指標。If each underlying table doesn't have a unique index and an ISO SCROLL cursor or if a Transact-SQLTransact-SQL KEYSET cursor is requested, the cursor is automatically a STATIC cursor.

如果 select_statement 包含 ORDER BY 子句,且該子句中的資料行不是唯一資料列識別碼,就會將 DYNAMIC 資料指標轉換成 KEYSET 資料指標。如果無法開啟 KEYSET 資料指標,便會轉換成 STATIC 資料指標。If select_statement contains an ORDER BY clause in which the columns aren't unique row identifiers, a DYNAMIC cursor is converted to a KEYSET cursor, or to a STATIC cursor if a KEYSET cursor can't be opened. 使用 ISO 語法但不含 STATIC 關鍵字時所定義的資料指標,也會進行這項處理。This process also occurs for a cursor defined by using ISO syntax but without the STATIC keyword.

READ ONLYREAD ONLY
防止利用這個資料指標來更新。Prevents updates from being made through this cursor. UPDATE 或 DELETE 陳述式中的 WHERE CURRENT OF 子句無法參考這個資料指標。The cursor can't be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. 這個選項會覆寫要更新之資料指標的預設功能。This option overrides the default capability of a cursor to be updated. 這個關鍵字有別於先前的 READ_ONLY,READ 和 ONLY 之間是空格,而不是底線。This keyword varies from the earlier READ_ONLY by having a space instead of an underscore between READ and ONLY.

UPDATE [OF column_name[ ,... n ] ]
在資料指標內定義可更新的資料行。Defines updatable columns within the cursor. 如果提供 OF column_name [ , ...n],則只允許修改所列出的資料行。If OF column_name [,...n] is supplied, only the columns listed allow modifications. 未提供任何清單時,除非資料指標已定義為 READ_ONLY,否則可以更新所有資料行。When no list is supplied, all columns can be updated, unless the cursor has been defined as READ_ONLY.

備註Remarks

在宣告變數之後,會將它初始化成 NULL。After a variable is declared, it's initialized to NULL. 請使用 SET 陳述式,將非 NULL 值指派給宣告的變數。Use the SET statement to assign a value that isn't NULL to a declared variable. 將值指派給變數的 SET 陳述式會傳回單一值。The SET statement that assigns a value to the variable returns a single value. 當您初始化多個變數時,每個區域變數都要使用個別的 SET 陳述式。When you initialize multiple variables, use a separate SET statement for each local variable.

您只能在運算式中使用變數,而不能使用物件名稱或關鍵字。You can use variables only in expressions, not instead of object names or keywords. 若要建構動態 Transact-SQLTransact-SQL 陳述式,請使用 EXECUTE。To construct dynamic Transact-SQLTransact-SQL statements, use EXECUTE.

SET @ cursor_variable 的語法規則不包括 LOCAL 和 GLOBAL 關鍵字。The syntax rules for SET @cursor_variable don't include the LOCAL and GLOBAL keywords. 當您使用 SET @ cursor_variable = CURSOR... 語法時,會將資料指標建立成 GLOBAL 或 LOCAL,這會視 [預設為本機資料指標資料庫] 選項的設定而不同。When you use the SET @cursor_variable = CURSOR... syntax, the cursor is created as GLOBAL or LOCAL, depending on the setting of the default to local cursor database option.

資料指標變數一律是區域變數,即使它們參考了全域資料指標也是如此。Cursor variables are always local, even if they reference a global cursor. 當資料指標變數參考全域資料指標時,資料指標會同時有全域和本機資料指標參考。When a cursor variable references a global cursor, the cursor has both a global and a local cursor reference. 如需詳細資訊,請參閱「範例 C」一節。For more information, see Example C.

如需詳細資訊,請參閱 DECLARE CURSOR (Transact-SQL)For more information, see DECLARE CURSOR (Transact-SQL).

您可以將複合指派運算子用於您在運算子右手邊有指派運算式的任何地方,其中包括變數以及 UPDATE、SELECT 和 RECEIVE 陳述式中的 SET。You can use the compound assignment operator anywhere you have an assignment with an expression on the right-hand side of the operator, including variables, and a SET in an UPDATE, SELECT, and RECEIVE statement.

請勿在 SELECT 陳述式中使用變數來串連值 (也就是計算彙總值)。Don't use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). 可能會發生非預期的查詢結果。Unexpected query results may occur. 因為 SELECT 清單中的所有運算式 (包括指派) 都不一定剛好針對每個輸出資料列執行一次。Because, all expressions in the SELECT list (including assignments) aren't necessarily run exactly once for each output row. 如需詳細資訊,請參閱這篇知識庫文章For more information, see this KB article.

權限Permissions

需要 public 角色中的成員資格。Requires membership in the public role. 所有使用者都可以使用 SET @ local_variableAll users can use SET @local_variable.

範例Examples

A.A. 列印利用 SET 來初始化的變數值Printing the value of a variable initialized by using SET

下列範例會建立 @myvar 變數,將字串值放入這個變數中,再列印 @myvar 變數的值。The following example creates the @myvar variable, puts a string value into the variable, and prints the value of the @myvar variable.

DECLARE @myvar char(20);  
SET @myvar = 'This is a test';  
SELECT @myvar;  
GO  

B.B. 使用以 SELECT 陳述式中的 SET 指派值的區域變數Using a local variable assigned a value by using SET in a SELECT statement

下列範例會建立一個名稱為 @state 的區域變數,且會在 SELECT 陳述式中使用這個區域變數來尋找在 Oregon 州的所有員工姓名。The following example creates a local variable named @state and uses the local variable in a SELECT statement to find the first and last names of all employees who live in the state of Oregon.

USE AdventureWorks2012;  
GO  
DECLARE @state char(25);  
SET @state = N'Oregon';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City  
FROM HumanResources.vEmployee  
WHERE StateProvinceName = @state;  

C.C. 針對區域變數使用複合指派Using a compound assignment for a local variable

下列兩個範例會產生相同的結果。The following two examples produce the same result. 它們會建立一個名為 @NewBalance 的區域變數,並將它乘以 10,然後將區域變數的新值顯示在 SELECT 陳述式中。They create a local variable named @NewBalance, multiplies it by 10 and displays the new value of the local variable in a SELECT statement. 第二個範例會使用複合指派運算子。The second example uses a compound assignment operator.

/* Example one */  
DECLARE  @NewBalance  int ;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT  @NewBalance;  
  
/* Example Two */  
DECLARE @NewBalance int = 10;  
SET @NewBalance *= 10;  
SELECT @NewBalance;  

D.D. 搭配全域資料指標來使用 SETUsing SET with a global cursor

下列範例會建立一個區域變數,再將資料指標變數設成全域資料指標名稱。The following example creates a local variable and then sets the cursor variable to the global cursor name.

DECLARE my_cursor CURSOR GLOBAL   
FOR SELECT * FROM Purchasing.ShipMethod  
DECLARE @my_variable CURSOR ;  
SET @my_variable = my_cursor ;   
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable  
--(@my_variable) set to the my_cursor cursor.  
DEALLOCATE my_cursor;   
--There is now only a LOCAL variable reference  
--(@my_variable) to the my_cursor cursor.  

E.E. 利用 SET 來定義資料指標Defining a cursor by using SET

下列範例會利用 SET 陳述式來定義資料指標。The following example uses the SET statement to define a cursor.

DECLARE @CursorVar CURSOR;  
  
SET @CursorVar = CURSOR SCROLL DYNAMIC  
FOR  
SELECT LastName, FirstName  
FROM AdventureWorks2012.HumanResources.vEmployee  
WHERE LastName like 'B%';  
  
OPEN @CursorVar;  
  
FETCH NEXT FROM @CursorVar;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    FETCH NEXT FROM @CursorVar  
END;  
  
CLOSE @CursorVar;  
DEALLOCATE @CursorVar;  

F.F. 從查詢中指派值Assigning a value from a query

下列範例會利用查詢來指派變數值。The following example uses a query to assign a value to a variable.

USE AdventureWorks2012;  
GO  
DECLARE @rows int;  
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);  
SELECT @rows;  

G.G. 修改此型別的屬性來指派使用者定義型別的變數值Assigning a value to a user-defined type variable by modifying a property of the type

下列範例會修改類型之 Point 屬性的值來設定使用者定義型別 X 的值。The following example sets a value for user-defined type Point by modifying the value of the property X of the type.

DECLARE @p Point;  
SET @p.X = @p.X + 1.1;  
SELECT @p;  
GO  

H.H. 叫用此型別的方法來指派使用者定義型別的變數值Assigning a value to a user-defined type variable by invoking a method of the type

下列範例會叫用使用者定義型別 pointSetXY 方法來設定該型別的值。The following example sets a value for user-defined type point by invoking method SetXY of the type.

DECLARE @p Point;  
SET @p=point.SetXY(23.5, 23.5);  

I.I. 建立 CLR 型別的變數,並呼叫 mutator 方法Creating a variable for a CLR type and calling a mutator method

下列範例會建立 Point 類型的變數,然後在 Point 中執行 mutator 方法。The following example creates a variable for the type Point, and then executes a mutator method in Point.

CREATE ASSEMBLY mytest from 'c:\test.dll' WITH PERMISSION_SET = SAFE  
CREATE TYPE Point EXTERNAL NAME mytest.Point  
GO  
DECLARE @p Point = CONVERT(Point, '')  
SET @p.SetXY(22, 23);  

範例:Azure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse)平行處理資料倉儲Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse) and 平行處理資料倉儲Parallel Data Warehouse

J.J. 列印利用 SET 來初始化的變數值Printing the value of a variable initialized by using SET

下列範例會建立 @myvar 變數,將字串值放入這個變數中,再列印 @myvar 變數的值。The following example creates the @myvar variable, puts a string value into the variable, and prints the value of the @myvar variable.

DECLARE @myvar char(20);  
SET @myvar = 'This is a test';  
SELECT top 1 @myvar FROM sys.databases;  
  

K.K. 使用以 SELECT 陳述式中的 SET 指派值的區域變數Using a local variable assigned a value by using SET in a SELECT statement

下列範例會建立一個名稱為 @dept 的區域變數,且會在 SELECT 陳述式中利用這個區域變數來尋找在 Marketing 部門中工作的所有員工的姓名。The following example creates a local variable named @dept and uses this local variable in a SELECT statement to find the first and last names of all employees who work in the Marketing department.

-- Uses AdventureWorks  
  
DECLARE @dept char(25);  
SET @dept = N'Marketing';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name  
FROM DimEmployee   
WHERE DepartmentName = @dept;  

L.L. 針對區域變數使用複合指派Using a compound assignment for a local variable

下列兩個範例會產生相同的結果。The following two examples produce the same result. 它們會建立一個名為 @NewBalance 的區域變數,並將它乘以 10,然後將區域變數的新值顯示在 SELECT 陳述式中。They create a local variable named @NewBalance, multiplies it by 10 and displays the new value of the local variable in a SELECT statement. 第二個範例會使用複合指派運算子。The second example uses a compound assignment operator.

/* Example one */  
DECLARE  @NewBalance  int ;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT  TOP 1 @NewBalance FROM sys.tables;  
  
/* Example Two */  
DECLARE @NewBalance int = 10;  
SET @NewBalance *= 10;  
SELECT TOP 1 @NewBalance FROM sys.tables;  

M.M. 從查詢中指派值Assigning a value from a query

下列範例會利用查詢來指派變數值。The following example uses a query to assign a value to a variable.

-- Uses AdventureWorks  
  
DECLARE @rows int;  
SET @rows = (SELECT COUNT(*) FROM dbo.DimCustomer);  
SELECT TOP 1 @rows FROM sys.tables;  

另請參閱See Also

複合運算子 (Transact-SQL) Compound Operators (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
SET 陳述式 (Transact-SQL)SET Statements (Transact-SQL)