SET @local_variable (Transact-SQL)
Sets the specified local variable, previously created by using the DECLARE @local_variable statement, to the specified value.
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 ] ] } ]
}
}
}
After a variable is declared, it is initialized to NULL. Use the SET statement to assign a value that is not NULL to a declared variable. The SET statement that assigns a value to the variable returns a single value. When you initialize multiple variables, use a separate SET statement for each local variable.
Variables can be used only in expressions, not instead of object names or keywords. To construct dynamic Transact-SQL statements, use EXECUTE.
The syntax rules for SET @cursor_variable do not include the LOCAL and GLOBAL keywords. When the SET @cursor_variable = CURSOR... syntax is used, 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. For more information, see Example C.
For more information, see DECLARE CURSOR (Transact-SQL).
The compound assignment operator can be used 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.
A. Printing the value of a variable initialized by using SET
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. Using a local variable assigned a value by using SET in a SELECT statement
The following example creates a local variable named @state and uses this local variable in a SELECT statement to find the first and last names of all employees who reside 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. Using a compound assignment for a local variable
The following two examples produce the same result. 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. Using 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. Defining a cursor by using 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. 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. Assigning a value to a user-defined type variable by modifying a property of the type
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. Assigning a value to a user-defined type variable by invoking a method of the type
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. Creating a variable for a CLR type and calling a mutator method
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);
