SQL Server Stored Procedures And Cursors

Here is a quick example of how to create a CURSOR in a SQL Server 2000 stored procedure. CURSORS are very similar to an ADO recordset. However, it does not require you to return any records back into ADO before processing the results.

Here is a quick example of how to create a CURSOR in a SQL Server 2000 stored procedure. CURSORS are very similar to an ADO recordset. However, it does not require you to return any records back into ADO before processing the results.

Be aware that CURSORS are somewhat resource intensive. So use it when necessary but always look for a better way. Personally, I try to limit utilization of cursors to scheduled jobs that aren't being run thousands of times during the day.
In this example, I chose the FAST_FORWARD option to optimize performance because it creates a forward only, read only cursor.

Also remember to CLOSE and DEALLOCATE your CURSORS as soon as possible to free up resources. Here's the code sample:



CREATE PROCEDURE sp_Test()
as

DECLARE @colA nvarchar(10)
DECLARE @colB nvarchar(10)
DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select colA,colB
From tableA

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ColA
PRINT @ColB
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB
END

CLOSE @MyCursor
DEALLOCATE @MyCursor
GO


Submission Date:  9/23/2005 2:59:03 PM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com

By Robbe Morris   Popularity  (3152 Views)
Picture
Biography - Robbe Morris
Robbe has been a Microsoft MVP in C# since 2004. He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.  Robbe also loves to scuba dive and go deep sea fishing in the Florida Keys or off the coast of Daytona Beach. Microsoft MVP