Post reply

Cursor

  • December 29, 2004 at 3:23 am

    #66378

    How do i declare a cursor by executing a stored proc ???

    Thanks in advance.

    regards

    Praveen

     

     

     

     

     

  • December 29, 2004 at 3:56 am

    #535224

    I'm not sure if I understand you. Can you give an example in pseudocode of what you mean?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 29, 2004 at 4:26 am

    #535225

    Frank,

    Iam having a stored proc which returns me a set of records and i want to do some processing on this retrieved records, for which i want to declare a cursor.

    I tried to declare it like this

    Declare crsX Cursor For

    Exec usp_X

    but its not working.

    Is there any possibilty to declare a cursor by executing a stored proc ??

    Regards

    Praveen

  • December 29, 2004 at 4:56 am

    #535226

    I think your syntax isn't supported by SQL Server (At least, have I never heard of that). The use of a cursor might be questionable, maybe you can workaround the cursor with INSERT INTO...EXEC statement.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 30, 2004 at 9:27 am

    #535416

    Praveen,

    If I understand correctly, you want to execute a stored procedure from within a SQL block (or another stored procedure) and work with the result set.

    You need to execute the procedure and insert the results into a temp table.  You can then work with the temp table using query statements or cursors, depending on your requirements.

    You can also use the openrowset or openquery to get at the results of a stored procedure.  I particularly do not like this method since you are "hard coding" access to the remote database (which in this case, would be itself) and I believe you end up using another connection resource to the database.

    Here's the temp table example.

    -- code to create the temp table would be here

    {..create a table with the necessary columns to match the result set..}

    --INSERT...EXECUTE procedure example

    INSERT #author_sales EXECUTE get_author_sales

    - manash

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

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