Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    20

    Unanswered: how to use a cursor for the result of stored procedure.

    Hi,
    If i want to iterate over the results returned from the execution of a stored procedure how can i define the cursor in a stored procedure..

    declare cursor1 cursor for exec myproc

    or
    declare cursor1 cursor for myproc


    Plzz some one help me

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Cursors are usually (always?) a bad idea - they cause all sorts of performance bottlenecks etc in systems. What is it you're trying to actually do with the data? can it be done with multiple updates etc?

    Mike

  3. #3
    Join Date
    Apr 2008
    Posts
    20
    hi,
    actually i want to iterate on the data and filter the data,which i get from the execution of stored procedure. please suggest me a solution.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm just guessing how things are set up but perhaps one of the following:
    • Alter the original sproc and put the output in a table then just select from the table
    • Add parameters to the original sproc to optionaly restrict the final output.
    • Pass a where clause as a string to the original sproc and execute the sql dynamicaly.
    That's three options for you.

    Mike

  5. #5
    Join Date
    Apr 2008
    Posts
    20
    hi ,
    actually here filtering means i want to iterate on each row..
    for example if the query returns 10 rows..i want to get the rows from 6th to 10th something like this.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    declare @last_name_shown = ''
    
    set rowcount 10
    	select * from MyTable
    	where name > @last_name_shown
    	order by name
    set rowcount 0
    This will show 10 names at a time
    If you set @last_name_shown to be the last name shown then it will show the next 10 names.

Posting Permissions

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