Post reply

Pass a rowset to a nested stored procedure?

  • February 6, 2006 at 2:24 am

    #364907

    Hi,

    I want to pass a rowset to a nested stored procedure (actually a sigle dimensioned array of record-ID's would be enough).

    I can cursor through a #temptable and repeatedly EXEC the sp but that's ugly and probably time-expensive; I'd much rather throw the sp  a table/rowset.

    Any ideas please ?

  • February 6, 2006 at 2:31 am

    #618912

    You could access the temptable inside your stored procedure. Or you could pass the record-ID's to the stored procedure in a comma separated string.

    If this doesn't help, please give us more information. Are you calling the stored procedure from T-SQL code or from a .NET-environment, or something else?

     

  • February 6, 2006 at 2:56 am

    #618913

    The nested sp is called by "EXECUTE spname" in T-SQL. I like the idea of referencing a temp table created in the "main"-sp in the called-sp but how can I reference it there? Surely I need to pass the information as a parameter or some such. I assume I cannot reference the #temptable by name in my called-sp. If I try to create an sp containing say SELECT * FROM #MyTempTable I get an error, understandably, since #MyTempTable is not seen as existing.

    [In this scenario, what is the scope of a (single)# temptable incidently?]

  • February 6, 2006 at 3:09 am

    #618914

    It is probably better to create a permanent table like the following:

    testtable(SPID int, RecordId int)

    Then, when you insert data into it, do as follows:

    delete testtable where SPID = @@SPID

    insert testtable select @@SPID, recordid

    To retrieve the recordid's in your second sp, do this:

    select * from testtable where SPID = @@SPID

     

    It is also possible to use the temp table, although I wouldn't recommend it (now that I think about it again), as I think the testproc sp will be recompiled on every call to the procedure, in order to adjust the execution plan to the current structure of the temp table. But if you insist, here's some demo code

    create proc testproc

    as

    begin

      select * from #temp

    end

    go

    create table #temp(recordid int)

    insert #temp select 2

    exec testproc

    drop table #temp

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply