June 10, 2009 at 1:30 am
guptaopus (3/10/2008)
Hi,I would like to use a stored procedure that calls other stored procedures and use a
temporary table to pass the results back and forth.(from Parent to child)
anybody help me doing it....
June 10, 2009 at 1:34 am
guptaopus (3/10/2008)
Hi,i need a anser for copy a table in a temp table and call back the table and compare the temp and get the result
June 10, 2009 at 7:53 am
reachviji83 (6/10/2009)
guptaopus (3/10/2008)
Hi,I would like to use a stored procedure that calls other stored procedures and use a
temporary table to pass the results back and forth.(from Parent to child)
anybody help me doing it....
You have several choices, depending on your requirements. Some of the choices and some of their benefits/problems:
1: create the temp table inside your stored procedure (create table #temptable ....), and refer to it by name in the procedure this procedure is calling. Disadvantages include recompilations, on 2005 this is better. A very simple example:
create proc p as
begin
create table #f (a int)
exec p2
select * from #f
end
go
create proc p2 as
begin
insert into #f values (1)
end
go
exec p
2: if you are on 2008 (and I'd prefer this solution when the amount of data is not too much): use table variables (search for table valued parameters and you will find many articles. This will work on 2008 only.
3: if your input is in XML, you may as well continue using XML for passing the structured data.
4: What data are you trying to pass? If it is simple (can be put into an array), you can use comma separated values in a varchar (search for handling arrays in SQL Server). Ugly, but common for simple data.
5: use a communication table (create a normal table with the desired schema, plus a column for identifying a concurent execution (I usually store the SPID in this column). Advantages: no proc recompilations, disadvantages, you need to handle cleaning up the table.
So there are many ways you can achieve what you would like (apart from 2 most of them are not very elegant). What are your requirements? Amount of data, estimated frequency of execution for the stored procedure, does it really have to be in the database? version of SQL Server, where is the data coming from 🙂 If you provide more info people here would be able to give better suggestions.
Regards,
Andras
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply