.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Steve Jackman
Imran Ghani
Santhakumar Munuswamy

Home >> Code Snippets >> Visual Studio >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Avoiding cursor in sql server

Posted By:Ramesh P       Posted Date: October 19, 2010    Points: 15    Category: Visual Studio    URL: http://www.dotnetspark.com  

This code snippets helps to understand how to avoid cursor to improve query performance.
 

Here, first one is a example of cursor. In second example, while loop is used instead of cursor to improve query performance.



An example of a SQL Cursor that we want to avoid

DECLARE @CustomerID int
DECLARE @FirstName varchar(30), @LastName varchar(30)
-- declare cursor called ActiveCustomers
DECLARE ActiveCustomers Cursor FOR
 SELECT CustomerID, FirstName, LastName 
 FROM Customer
 WHERE Active = 1
-- Open the cursor
OPEN ActiveCustomers
-- Fetch the first row of the cursor and assign its values into variables
FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName 
-- perform action whilst a row was found
WHILE @@FETCH_STATUS = 0
BEGIN
 Exec MyStoredProc @CustomerID, @Forename, @Surname
 -- get next row of cursor
 FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName 
END
-- Close the cursor to release locks
CLOSE ActiveCustomers
-- Free memory used by cursor
DEALLOCATE ActiveCustomers

Cursor alternative 1: Using the SQL WHILE loop

SQL provides us with the WHILE looping structure. This can be utilised with a temporary table that enables us to avoid using a cursor:

-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE #ActiveCustomer (
 RowID int IDENTITY(1, 1), 
 CustomerID int,
 FirstName varchar(30),
 LastName varchar(30)
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @CustomerID int, @FirstName varchar(30), @LastName varchar(30)

-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #ActiveCustomer (CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE Active = 1 

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @CustomerID = CustomerID, @FirstName = FirstName, @LastName = LastName 
 FROM #ActiveCustomer
 WHERE RowID = @RowCount

 EXEC MyStoredProc @CustomerID, @FirstName, @LastName

 SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #ActiveCustomer

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend