Completing the Introduction to Transact-SQL - Playing with Cursors
(Page 3 of 4 )
Playing with Cursors
You may ask what we can do with cursors. Cursors are really useful because we can access data on a row-by-row basis. Each time we use the SELECT statement, the table is returned as a whole, including all of the rows. However, from time to time a situation may arise where we need to work with rows independently.
What's more, with the help of cursors we can move throughout the table on a row-by-row basis using the return values of the SELECT statement. That's how we can work on the rows- modify, delete, and so forth. There are specific steps which must be followed when working with cursors:
Declaration of variables to store the return row-values of the SELECT statement.
Declaration of the cursor (by pointing out the appropriate SELECT statement).
Opening the cursor.
Accessing the rows (data) using the cursors.
Closing the cursor (and also de-allocate it if you won't need it anymore).
Keep in mind that the cursors allow us sequential forward movements only. There's no way you could step backwards. So be aware of how many rows you fetch forward.
Check out the following real-world example where we implement cursors to print the ProdId, ProdName, and UnitPrice columns of the table called Products.
Use NameOfDatabase
-- 1. Declaration of variables.
DECLARE @MyProdID smallint
DECLARE @MyProdName nvarchar(20)
DECLARE @MyUnitPrice smallmoney
-- 2. Declaration of the cursor.
DECLARE ProdCursor CURSOR FOR
SELECT ProdID, ProdName, UnitPrice
FROM Products
WHERE ProdID <= 10
-- 3. Opening the cursor.
OPEN ProdCursor
-- 4. Accessing the rows from the cursor.
FETCH NEXT FROM ProdCursor
INTO @MyProdID, @MyProdName, @MyUnitPrice
PRINT '@MyProdID = ' + CONVERT(nvarchar, @MyProdID)
PRINT '@MyProdName = ' + CONVERT(nvarchar, @MyProdName)
PRINT '@MyUnitPrice = ' + CONVERT(nvarchar, @MyUnitPrice)
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM ProdCursor
INTO @MyProdID, @MyProdName, @MyUnitPrice
PRINT '@MyProdID = ' + CONVERT(nvarchar, @MyProdID)
PRINT '@MyProdName = ' + CONVERT(nvarchar, @MyProdName)
PRINT '@MyUnitPrice = ' + CONVERT(nvarchar, @MyUnitPrice)
END
-- 5. Closing and de-allocating the cursor.
CLOSE ProdCursor
DEALLOCATE ProdCursor
As you can see from the above example, the variables that are declared must be of the same data type as the columns from the database. We declare the cursor using the CURSOR FOR... FROM... WHERE construct of code. As soon as the cursor is opened using the OPEN instruction, the SELECT instruction is executed right away.
Thereafter, as I mentioned at the beginning of this chapter, we use the FETCH instruction to move on a row-by-row basis throughout the table. There's the variable @@FETCH_STATUS which is used in that WHILE loop. It can have one of the following return values: 0 when the FETCH statement was successfully executed; -1 when an error occurred during the FETCH execution; -2 when the required row does not exist.
The CLOSE statement closes the cursor, freeing up if there are any cursor locks, but leaves it available for reopening. DEALLOCATE removes the cursor from the reference. After de-allocation the cursor can be reopened only if you re-declare it again.
Oh, and if you really paid attention, then you may have noticed from the example above that I used the two hyphens (--) to introduce comments. They can be used for single-line comments; you just write them at the beginning of the line that's going to be commented. But we can also use multi-line comments with the /* ... */ block. Comments improve the readability of the code; they are not evaluated nor executed.
Next: The World of Functions >>
More MS SQL Server Articles
More By Barzan "Tony" Antal
