Home Forums SQL Server 2012 SQL Server 2012 - T-SQL sql cursor in stored procedure - not looping RE: sql cursor in stored procedure - not looping

  • July 12, 2018 at 12:35 pm

    #1997384

    sgmunson - Thursday, July 12, 2018 12:06 PM

    sani.bozic - Thursday, July 12, 2018 9:28 AM

    Thank you once again for your help. I went with creating 2 separate procedures, it does the job.

    You're very welcome.   And it now appears there IS a way to avoid it.   See Scott Pletcher's post above.  I had forgotten about testing the @@FETCH_STATUS for -1.   I wouldn't be all that worried about it not being considered standard.   It does eliminate the duplicated code.   However, I always prefer to avoid cursors unless there simply is NO other way to do something.   You may wish to see whether you actually need one or not.   There is almost always another way to skin the same cat without a cursor.

    Yeah, -2 can get you if you just code "@@FETCH_STATUS <> 0".  By default, "If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor."  And I've seen some cases where that caused cursors to exit well before the entire set was processed. 

    So one needs to either explicitly force the cursor to not be able to get a -2 (rather tricky for many developers to be sure of anyway) or code an explicit check for it.  I just always code for it, then I never get unexpectedly burned even if the cursor reverts to dynamic from some changes to it.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.