Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

procedure having output as cursor

sikkilamsikkilam Member Posts: 1
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

Comments

  • elko72elko72 Member Posts: 1
    : 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
Sign In or Register to comment.