Question regarding SP

[Patrick]

Hi guys

I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?

Thanks

 

[David Gugick]

Hi guys

I have written one stored procedure. At the end it returns the set of
rows. I want to define cursor on the result which is sent by stored
procedure. How can i do that ?

Thanks

How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?

Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.

 

[Allan Mitchell]

Seeing as you are asking this in the DTS group, amongst others, you may want to look at

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com

 

[Patrick]

I Want to use cursor with in the stored procedure itself.

My main problem is how can I catch the result returned by one procedure in
another procedure.

Marmik

 

[David Gugick]

I Want to use cursor with in the stored procedure itself.

My main problem is how can I catch the result returned by one
procedure in another procedure.

Marmik

Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.

Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.

 

[Chris Hayes]

Hello,

What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?

Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?

If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.

Chris

I Want to use cursor with in the stored procedure itself.

My main problem is how can I catch the result returned by one procedure in
another procedure.

Marmik

 

[Sarav]

Here you go...
create table TestTable

( id int not null,

name varchar(50) not null)

go

Insert into TestTable values (1,'Test Name 1')

Insert into TestTable values (2,'Test Name 2')

Insert into TestTable values (3,'Test Name 3')

Insert into TestTable values (4,'Test Name 4')

go

Create Proc TestProc1

as

Select * from TestTable

go

Create Proc TestProc2

as

Declare @tmpTable table

( id int not null,

name varchar(50) not null)



Insert into @tmpTable Exec TestProc1

Select *,'Result From Proc2' from @tmpTable





Run this example script ... I hope it anser your question.

-Sarav

 

[Gopinath Rajee]

You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.

Gopi

I Want to use cursor with in the stored procedure itself.

My main problem is how can I catch the result returned by one procedure in
another procedure.

Marmik