DECLARE CURSOR

aorozco

Hi, I was looking for a DECLARE CURSOR snippet but I couldn't find one, so I decided to create this basic one.

DECLARE $CURSOR$--@Variables

DECLARE /*Cursor name*/ CURSOR
FOR /*Select Statement*/
OPEN /*Cursor Name*/
FETCH NEXT FROM /*Cursor Name*/
INTO --@Variables

WHILE @FETCH_STATUS = 0
BEGIN

/*SQL Statements*/

FETCH NEXT FROM /*Cursor Name*/
INTO --@Variables
END
CLOSE /*Cursor Name*/
DEALLOCATE /*Cursor Name*/

I hope it helps!

Giggles220

Thanks, no matter how many times I need to write cursor code, I always cut and paste an example to make sure I get it all. This snippet will be very helpful.

EdCarden

DO you use cursors enough that you would benefit from having a Cursor code snippet in SQL Prompt?

Cursors are a necessary evil in SQL that more often then ot are heavily over used by traditional procedural/OOP developers. There are valid scenarios for using cursors but they are far and few. If your using cursors enough that having a predefined code snippet would be of benefit then you should look take some time to look at alternatives to cursor use.

BTW - I mean no offense with the above; just comenting on how you could help yourself improve things by avoiding excess cursor use.

datacentricity

An alternative view might be that if one uses cursors so rarely as to need a snippit to remeber all the correct syntax that's a good thing.

ofrede

I know this is an old post, but I have taken the liberty to improve it a little. Now you will be prompted for the cursor name, so you dont have to copy/paste it down through the code.

DECLARE $CURSOR$--@Variables

DECLARE <Cursor_Name, varname, Cursor_Name> CURSOR
FOR /*Select Statement*/
OPEN <Cursor_Name, varname, Cursor_Name>
FETCH NEXT FROM <Cursor_Name, varname, Cursor_Name>
INTO --@Variables

@FETCH_STATUS = 0
BEGIN

/*SQL Statements*/

FETCH NEXT FROM <Cursor_Name, varname, Cursor_Name>
INTO --@Variables
END
CLOSE <Cursor_Name, varname, Cursor_Name>
DEALLOCATE <Cursor_Name, varname, Cursor_Name>

EdCarden

datacentricity wrote:

An alternative view might be that if one uses cursors so rarely as to need a snippit to remeber all the correct syntax that's a good thing.

I get what you're saying but the point or purpose for creating Code Snippets is so you have quick access to frequently used code, not access to seldom used code. I doubt the author of this thread was creating a snippet for a seldom used cursor

jac

Actually, that's precisely why I am looking at this page