Post reply

Sp_Execute with cursors

  • December 6, 2008 at 3:49 am

    #73122

    Hi,

    This is my first post.

    Here is my code.

    Declare

    @SqlStr1 Nvarchar(4000)

    @SqlStr2 Nvarchar(4000)

    @cCursor Cursor

    Set @SqlStr1 =

    N'Set Quoted Identifier Off

    Set @myCursor = Cursor for

    Select ---------------------------- goes on'

    Set @SqlStr2 =

    N'Left outer join --------------

    -- Then after that i am opening the cursor

    Open @myCursor'

    I try to execute the code like this

    Exec('Exec Sp_ExecuteSql N''' + @SqlStr1 + @SqlStr2 + ''',N''@myCursor Cursor Output'',N''' + @cCursor + ''' Output')

    When i excute the code this is the error message that i get

    The variable '@cCursor ' is a cursor variable, but it is used in a place where a cursor variable is not valid.

    Any help would be really appreciated,

    Thanks,

    Mitchell

    Mitchell Clive Fernandez

  • December 6, 2008 at 5:19 am

    #908803

    Hi Mitchy

    While there's a chance that this code could be tweaked to work, the first question is "what are you trying to do?"

    Can you explain this in words e.g. "I want to get a list of all clients who haven't purchased anything in the last six months and send them an email"?

    Also, could you post the guts of your existing query without the overhead of attempting to run it as dynamic SQL? This would be very helpful.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • December 6, 2008 at 4:19 pm

    #908860

    Dynamic SQL to run a cursor... how slow do you actually want things to run?

    I agree with what the others have already stated... it would be fair better to state the business rules of what you're trying to do and let us make a suggestion or two as to how to write code to do it because 99.9999 percent of the time, you don't need a cursor and out of the .0001 percent that remains, you might need a dynamic cursor 1 out of a billion times.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 6, 2008 at 6:01 pm

    #908878

    Mitchell, cursor is not a variable, it cannot be returned as output parameter.

    You're trying to use something what exists in other languages but not in SQL.

    Explain what you need to do and we could suggest you right way to do it.

    _____________
    Code for TallyGenerator

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply