Question regarding SP

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 ?
ThanksPatrick wrote:
> 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.
David Gugick
Imceda Software
www.imceda.com|||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" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> 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
>|||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" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
>> 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.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> 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.
David Gugick
Imceda Software
www.imceda.com|||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
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> 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
>|||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
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>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" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> 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.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>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" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> 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.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>