SET @local_variable (T-SQL)

Sets the specified local variable, previously created with the DECLARE @local_variable statement, to the given value.

Syntax

SET
    {
        {@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]] } ]
                         }
                    }
            }
    }

Arguments

@local_variable

Is the name of a variable of any type except cursor. Variable names must begin with one at sign (@). Variable names must conform to the rules for identifiers. For more information, see Using Identifiers.

expression

Is any valid Microsoft® SQL Server™ expression.

cursor_variable

Is the name of a cursor variable. If the target cursor variable previously referenced a different cursor, that previous reference is removed.

cursor_name

Is the name of the a cursor declared using the DECLARE CURSOR statement.

CURSOR

Specifies that the SET statement contains a declaration of a cursor.

SCROLL

Specifies that the cursor supports all fetch options (FIRST, LAST, NEXT, PRIOR, RELATIVE, and ABSOLUTE). SCROLL cannot be specified if FAST_FORWARD is also specified.

FORWARD_ONLY

Specifies that the cursor supports only the FETCH NEXT option. The cursor can be retrieved only in one direction, from the first to the last row. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor is implemented as DYNAMIC. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. FAST_FORWARD and FORWARD_ONLY are mutually exclusive, if one is specified the other cannot be specified.

STATIC

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

KEYSET

Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). 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 resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

DYNAMIC

Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The absolute and relative fetch options are not supported with dynamic cursors.

FAST_FORWARD

Specifies a FORWARD_ONLY, READ_ONLY cursor with optimizations enabled. FAST_FORWARD cannot be specified if SCROLL is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive, if one is specified the other cannot be specified.

READ_ONLY

Prevents updates from being made through this cursor. The cursor cannot 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 LOCKS

Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.

OPTIMISTIC

Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead 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 it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNING

Specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another.

FOR select_statement

Is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within the select_statement of a cursor declaration.

If DISTINCT, UNION, GROUP BY, or HAVING are used, or an aggregate expression is included in the select_list, the cursor will be created as STATIC.

If each of the underlying tables does not have a unique index and an SQL-92 SCROLL cursor or a Transact-SQL KEYSET cursor is requested, it will automatically be a STATIC cursor.

If select_statement contains an ORDER BY in which the columns are not unique row identifiers, a DYNAMIC cursor is converted to a KEYSET cursor, or to a STATIC cursor if a KEYSET cursor cannot be opened. This also happens for a cursor defined using SQL-92 syntax but without the STATIC keyword.

READ ONLY

Prevents updates from being made through this cursor. The cursor cannot 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. 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. If OF column_name [,...n] is supplied, only the columns listed will allow modifications. If no list is supplied, all columns can be updated, unless the cursor has been defined as READ_ONLY.

Remarks

After declaration, all variables are 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 initializing multiple variables use a separate SET statement for each local variable.

Variables can be used only in expressions, not in place of object names or keywords. To construct dynamic 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.

Permissions

SET @local_variable permissions default to all users.

Examples

A. Print the value of a variable initialized with SET

This example creates the @myvar variable, places 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. Use a local variable assigned a value with SET in a SELECT statement

This example creates a local variable named @state and uses this local variable in a SELECT statement to find all author first and last names where the author resides in the state of Utah.

USE pubs

GO

DECLARE @state char(2)

SET @state = 'UT'

SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS Name, state

FROM authors

WHERE state = @state

GO

C. Use SET with a global cursor

This example creates a local variable and then sets the cursor variable to the global cursor name.

DECLARE my_cursor CURSOR GLOBAL FOR SELECT * FROM authors

   DECLARE @my_variable CURSOR

   SET @my_variable = my_cursor 
                     /* There is a GLOBAL declared
                        reference (my_cursor) and a LOCAL variable
                        reference (@my_variable) to the my_cursor
                        cursor.                                    */

   DEALLOCATE my_cursor  /* There is now only a LOCAL variable
                        reference (@my_variable) to the my_cursor
                        cursor.                                    */

D. Define a cursor with SET

This example uses the SET statement to define a cursor.

DECLARE @CursorVar CURSOR

SET @CursorVar = CURSOR SCROLL DYNAMIC

FOR

SELECT LastName, FirstName

FROM Northwind.dbo.Employees

WHERE LastName like 'B%'

OPEN @CursorVar

FETCH NEXT FROM @CursorVar

WHILE @@FETCH_STATUS = 0

BEGIN

    FETCH NEXT FROM @CursorVar

END

CLOSE @CursorVar

DEALLOCATE @CursorVar

E. Assign a value from a query

This example uses a query to assign a value to a variable.

USE Northwind

GO

DECLARE @rows int

SET @rows = (SELECT COUNT(*) FROM Customers)

See Also