although CURSOR and FETCH is classified as ‘evil‘ in SQL server, but sometimes we cannot avoid using it. Cause in some scenarios, pivot and other similiar method to perform looping and transformation is not sufficient enough. here’s a link of good example implementing cursor.
all example copy from microsoft sites,
http://msdn.microsoft.com/en-us/library/ms180152.aspx
A. Using FETCH in a simple cursor
The following example declares a simple cursor for the rows in the Person.Person table with a last name that starts with B, and uses FETCH NEXT to step through the rows. The FETCH statements return the value for the column specified in DECLARE CURSOR as a single-row result set.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE AdventureWorks2008R2; GO DECLARE contact_cursor CURSOR FOR SELECT LastName FROM Person.Person WHERE LastName LIKE 'B%' ORDER BY LastName; OPEN contact_cursor; -- Perform the first fetch. FETCH NEXT FROM contact_cursor; -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM contact_cursor; END CLOSE contact_cursor; DEALLOCATE contact_cursor; GO |
B. Using FETCH to store values in variables
The following example is similar to example A, except the output of the FETCH statements is stored in local variables instead of being returned directly to the client. The PRINT statement combines the variables into a single string and returns them to the client.
|
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 33 34 |
USE AdventureWorks2008R2; GO -- Declare the variables to store the values returned by FETCH. DECLARE @LastName varchar(50), @FirstName varchar(50); DECLARE contact_cursor CURSOR FOR SELECT LastName, FirstName FROM Person.Person WHERE LastName LIKE 'B%' ORDER BY LastName, FirstName; OPEN contact_cursor; -- Perform the first fetch and store the values in variables. -- Note: The variables are in the same order as the columns -- in the SELECT statement. FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName; -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- Concatenate and display the current values in the variables. PRINT 'Contact Name: ' + @FirstName + ' ' + @LastName -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName; END CLOSE contact_cursor; DEALLOCATE contact_cursor; GO |
C. Declaring a SCROLL cursor and using the other FETCH options
The following example creates a SCROLL cursor to allow full scrolling capabilities through the LAST, PRIOR, RELATIVE, and ABSOLUTE options.
|
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 |
USE AdventureWorks2008R2; GO -- Execute the SELECT statement alone to show the -- full result set that is used by the cursor. SELECT LastName, FirstName FROM Person.Person ORDER BY LastName, FirstName; -- Declare the cursor. DECLARE contact_cursor SCROLL CURSOR FOR SELECT LastName, FirstName FROM Person.Person ORDER BY LastName, FirstName; OPEN contact_cursor; -- Fetch the last row in the cursor. FETCH LAST FROM contact_cursor; -- Fetch the row immediately prior to the current row in the cursor. FETCH PRIOR FROM contact_cursor; -- Fetch the second row in the cursor. FETCH ABSOLUTE 2 FROM contact_cursor; -- Fetch the row that is three rows after the current row. FETCH RELATIVE 3 FROM contact_cursor; -- Fetch the row that is two rows prior to the current row. FETCH RELATIVE -2 FROM contact_cursor; CLOSE contact_cursor; DEALLOCATE contact_cursor; GO |