Pass a rowset to a nested stored procedure?
-
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 ?
-
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?
-
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?]
-
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