DECLARE CURSOR

aorozcoaorozco Posts: 2 New member
edited October 16, 2014 1:02PM in SQL Prompt code snippets
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!

Comments

  • 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.
  • EdCardenEdCarden Posts: 134 Silver 1
    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.
  • 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.
    "Your mind is like a parachute, it works best when open" Frank Zappa
    "Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
    blog: http://datacentricity.net
    twitter: @datacentricity
  • ofredeofrede Posts: 11 Bronze 1
    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>
  • EdCardenEdCarden Posts: 134 Silver 1
    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
  • jacjac Posts: 1 New member
    Actually, that's precisely why I am looking at this page ;)

Sign In or Register to comment.