Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    47

    Question Unanswered: Query in Cursors

    See If u could shed some light on the following...
    I am using Dynamic SQL in the follwing Cursor but it gives error That there is no cursor allocated for @OutputCursor

    CREATE PROCEDURE RetieveMultiTransferRecord --My Stored Proc Name
    --Parameters i M PASSING from my Prog.

    @ScrolNo as numeric(5),
    @EntCode as char(2),
    @TransactionTable as nvarchar(20),
    @MODOFTRX AS CHAR(2)
    AS
    BEGIN TRAN

    DECLARE @BranchID char(4)

    BEGIN
    DECLARE @OutputCursor Cursor

    Set @dynamicSQL='SELECT '+@TRANSACTIONTABLE+'.BRANCHID,'+@TRANSACTIONTABLE + FROM

    '+@TRANSACTIONTABLE+',ACCOUNTMASTER'+ ' WHERE '+@TransactionTable+'.ENTRYSTATUS = '+'"NEW"'+ 'and AccountMaster.CustID=

    '+@TransactionTable+'.CustID '+'and '+@TransactionTable+'.ScrolNo = '+@ScrolNo+'and '+@TransactionTable+'.ENTCode= '''+@ENTCODE+''' AND

    '+@TRANSACTIONTABLE+'.MODOFTRX= '''+@MODOFTRX+''' ORDER BY '+@TransactionTable+' .SCROLNO'


    Set @dynamicSQL='Set @OutputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +@dynamicSQL + ' ; OPEN @outputCursor'

    -- Execute dynamic sql

    exec sp_executesql -- sp_executesql will essentially create a sproc

    @dynamicSQL, -- The SQL statement to execute (body of sproc)

    N'@outputCursor CURSOR OUTPUT', -- The parameter list for the sproc: OUTPUT CURSOR

    @outputCursor OUTPUT -- The parameter to pass to the sproc: the CURSOR


    -- Code that will just output the values from the cursor


    FETCH NEXT FROM @outputCursor INTO @BranchID

    -- Loop while there're more things in the cursor

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @CustID

    FETCH NEXT FROM @outputCursor INTO @BranchID
    END




    CLOSE @outputCursor

    DEALLOCATE @outputCursor

    End
    COMMIT TRAN
    GO

    It always gives error That there is no cursor allocated for @OutputCursor
    Please help...

  2. #2
    Join Date
    Apr 2009
    Posts
    7
    I don't think that this is not the way to execute dynamic query.

    At the 'OPEN @outputCursor' statement: Cursor should have a query. At this point, it still considers query as a string and you cannot open a cursor with a string.

    You need to put cursor statements as well in the dynamic query. That means you need to build statement right from "declaring cursor" to "deallocating cursor" and then you have to execute the entire statement using sp_execute statement.

    Try it and let me know.
    Last edited by mvmanohar; 04-17-09 at 16:09.

  3. #3
    Join Date
    Mar 2009
    Posts
    47

    Question

    Do U mean I should be writing all the statements from Declaring Cursor to Deallocating it as an Dynamic SQL..I cant do it bcoz the values returned I use futher...I am sending U a link from where I got this...

    http://geekswithblogs.net/NewThingsI...-query.aspxBut it doesn't work in my case...Please let me know if u find some solution...

    Thnks in advance

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How's about you tell us what you're atually trying to achieve?
    Can you print @dynamicSQL and share the result please?
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2009
    Posts
    7
    Sneha,
    If you use 'temporary/global variables or table' then you will be able to use the results from the dynamic query.

    So much patience is required while working on Dynamic queries. Trust me, I had gone through all these. Just keep on trying couple of options.

    Let me know if temp/global variables suits well with your requirement. I'm unable to browse the link.

  6. #6
    Join Date
    Mar 2009
    Posts
    47
    Hi I got the solution the prob. was with the Dynamic SQL query....U r right one has to try a lot of combinations while using dynamic sql query...still I m not comfortable with the Single quotes and double quotes funda...thnks

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a whole other reason to avoid dynamic SQL and write properly constructed T-SQL on its own
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2009
    Posts
    7
    Good to hear that your problem is resolved. I can re-build a query for you if you can send me the query with which you are facing problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •