procedure having output as cursor

sikkilam

i have a requirement where in the procedure has cursor as output.
i want an example of a procedure with cursor as output and how to execute the procedure. please help me out
regards
kiran

elko72

: i have a requirement where in the procedure has cursor as output.
: i want an example of a procedure with cursor as output and how to execute the procedure. please help me out
: regards
: kiran
:
:
I have used this code before and it has worked for me.
See if it is any use to you.


CREATE procedure UseCursor
as
begin
set nocount on

--this table is used to store all the results for output
create table #resultsfinal
(Ref_1 char(5) not null,
Ref_2 char(5) not null,
Field_1 char(5) not null,
Field_2 char(5) not null,
Field_3 char(5) not null,)


declare @Ref_1 varchar(12)
declare @Ref_2 varchar(12)

-- this cursor is used to get a list of references from you Reference Table
declare Reflist_cur cursor
FOR SELECT Reference_1,Reference_2
FROM TblReference

-- this gets the list of References
open Reflist_cur

-- this line gets the first record from the cursor
fetch next from Reflist_cur into @Ref_1, @Ref_2

-- check the references that are returned bt the cursor
@fetch_status <> -1)
begin
@fetch_status <> -2)
begin
Insert into #resultsfinal(Ref_1, Ref_2, Field_1, Field_2, Field_3)
exec Store Procedure @Ref_1e, Ref_2
--This store procedure is selecting you reference info based on parameters
--being sent ot Store procedure
end
fetch next from Reflist_cur into @Ref_1, @Ref_2 end
close Reflist_cur
deallocate Reflist_cur

set nocount on

select Ref_1, Ref_2, Field_1, Field_2, Field_3
from #resultsfinal
end