by Andrei Hetel
25. February 2010 23:37
Cursors in SQL Server - don't like cursors! In this article I'll show you a method to avoid using them. Usually, cursors are using a lot of SQL Server resources and of course reduce the overall performance of the application. The only place where I can think of somebody would ever need cursors is for a row-by-row operation. A typical scenario looks like that. Let's suppose we have a contact list table and we want for every row having 'UpdatePhoneNo' field equal with 1 to perform some complex calculations. Using cursor approach, here is the code:
DECLARE @ContactId INT
DECLARE @PhoneNo VARCHAR(20)
DECLARE MyList CURSOR FOR
SELECT ContactId, PhoneNo
FROM Contact
WHERE UpdatePhoneNo = 1
OPEN MyList
FETCH NEXT FROM MyList INTO @ContactId, @PhoneNo
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC UpdateContactList @ContactId, @PhoneNo
FETCH NEXT FROM MyList INTO @ContactId, @PhoneNo
END
CLOSE MyList
DEALLOCATE MyList
One method is to use WHILE loops
-- Create a temporary table
-- Please note identity column
CREATE TABLE #TempContact (
TempID INT IDENTITY(1, 1),
ContactID INT,
PhoneNo VARCHAR(20))
-- Needed variables
DECLARE @TotRecords INT
DECLARE @Counter INT
DECLARE @ContactId INT
DECLARE @PhoneNo VARCHAR(20)
-- Put the data into temporary table
INSERT INTO #TempContact
SELECT ContactId, PhoneNo
FROM Contact
WHERE UpdatePhoneNo = 1
SET @TotRecords = @@ROWCOUNT
SET @Counter = 1
WHILE @Counter <= @TotRecords
BEGIN
SELECT @ContactId = ContactId, @PhoneNo = PhoneNo
FROM #TempContact
WHERE TempID = @Counter
EXEC UpdateContactList @ContactId, @PhoneNo
SET @Counter = @Counter + 1
END
-- drop temporary table
DROP TABLE #TempContact
Please take in account that here is not a solution to a real problem. Was used just to illustrate a concept that can help you improve the efficiency of your T-SQL programming.