SQL: Simple Cursor
Author: jason
Date: 2014-01-23 10:12:03
Category: Musing

I often forget the syntax for setting up a cursor. I don't really like using cursors. I don't use them a lot. But when I do, I prefer Dos Equis, wait, I mean simple cursors.

Here I made a Temp table a populated it with 2 rows, but didn't include a value for the date field. The Cursor will select each row and update it with a date. This could more easily be done with a normal UPDATE/SET statement. But think about having to run another Stored Procedure inside the cursor instead of an Update statement.
Code

DECLARE @Temp AS TABLE ([id] BIGINT, [name] VARCHAR(10), [date] DATE, [message] VARCHAR(MAX))
INSERT INTO @Temp SELECT 1, 'Apple', NULL, NULL
INSERT INTO @Temp SELECT 2, 'Peach', NULL, NULL

SELECT * FROM @Temp

DECLARE @id AS BIGINT
DECLARE SimpleCursor CURSOR FOR
SELECT id FROM @Temp

OPEN SimpleCursor
FETCH NEXT FROM SimpleCursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE t SET t.date = GETDATE() FROM @Temp t WHERE t.id = @id
FETCH NEXT FROM SimpleCursor INTO @id
END
CLOSE SimpleCursor
DEALLOCATE SimpleCursor

SELECT * FROM @Temp


The 1st Select statement shows the contents of the Temp table with no date values. Then the cursor runs. The 2nd Select shows the date is now populated on each row.




Now let's add some error handling instead the cursor. I've put a TRY CATCH inside the Cursor operation. I have also added a CASE statement which will attempt to put the letter "a" into the date field when the id is not 1.
Code

DECLARE @Temp AS TABLE ([id] BIGINT, [name] VARCHAR(10), [date] DATE, [message] NVARCHAR(4000))
INSERT INTO @Temp SELECT 1, 'Apple', NULL, NULL
INSERT INTO @Temp SELECT 2, 'Peach', NULL, NULL
DECLARE @id AS BIGINT
DECLARE SimpleCursor CURSOR FOR
SELECT id FROM @Temp
OPEN SimpleCursor
FETCH NEXT FROM SimpleCursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
UPDATE t SET t.date = (CASE WHEN @id = 1 THEN GETDATE() ELSE 'a' END)
FROM @Temp t WHERE t.id = @id
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
DECLARE @Error AS NVARCHAR(4000);
SELECT @Error = ERROR_MESSAGE();
UPDATE t SET message = @Error FROM @Temp t WHERE t.id = @id
END CATCH
FETCH NEXT FROM SimpleCursor INTO @id
END
CLOSE SimpleCursor
DEALLOCATE SimpleCursor

SELECT * FROM @Temp


The TRY/CATCH block processes the 1st operation just fine. When it gets to the 2nd it catches the conversion error and updates the message field.






jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009