/
cursor.sql
32 lines (30 loc) · 1003 Bytes
/
cursor.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
--Associate a SELECT statement to a cursor and define the
--cursor's characteristics
DECLARE MyCursor CURSOR
/*<cursor characteristics>*/
FOR SELECT adt.AddressTypeId
,adt.NAME
,adt.ModifiedDate
FROM Person.AddressType adt
--Open the cursor to access the result set returned by the
--SELECT statement
OPEN MyCursor
--Retrieve one row at a time from the result set returned by
--the SELECT statement
DECLARE @AddressTypeId INT
,@Name VARCHAR(50)
,@ModifiedDate DATETIME
FETCH NEXT FROM MyCursor INTO @AddressTypeId, @Name, @ModifiedDate
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'NAME = ' + @Name
--Optionally, modify the row through the cursor
UPDATE Person.AddressType
SET Name = Name + 'z'
WHERE CURRENT OF MyCursor
FETCH NEXT FROM MyCursor INTO @AddressTypeId, @Name, @ModifiedDate
END
--Close the cursor and release all resources assigned to the
--cursor
CLOSE MyCursor
DEALLOCATE MyCursor