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

mohanrao · #1 (**permalink**) 04-30-08, 01:31

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

mike_bike_kite · #2 (**permalink**) 04-30-08, 04:09

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

mohanrao · #3 (**permalink**) 04-30-08, 04:16

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.

mike_bike_kite · #4 (**permalink**) 04-30-08, 05:27

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

mohanrao · #5 (**permalink**) 04-30-08, 05:33

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.

mike_bike_kite · #6 (**permalink**) 04-30-08, 06:02

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech