From SQL-Server Documentation
Example
A. Output Variables Using SET Initialization
The next example creates a @myvar variable, placing a string value in the variable and outputs the value of the @myvar variable.
Declare @myvar char (20)
Set @MYVAR = 'this is a test'
SELECT @MYVAR
Go
B. Use local variables assigned by set in the SELECT statement
The following example creates a local variable named @State and uses the local variable in the SELECT statement to find all the authors living in 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. Using SET for global cursors
The following example creates a local variable and sets the cursor variable to a global tag name.
Declare My_CURSOR CURSOR GLOBAL for SELECT * 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. Use the set definition cursor
The following example defines a cursor using the SET statement.
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 value by query
The following example uses the query to assign a value.
Use northwind
Go
Declare @Rows Int
Set @Rows = (Select Count (*) from Customers)
See
Decloy @local_variable Execute Expression SELECT SET uses variables and parameters
© 1988-2000 Microsoft Corporation. all rights reserved.
