Stored Procedure Within Stored Procedure - Getting the result set
-
If I have a stored procedure that calls another stored procedure and the second stored procedure fills a temp table and does a select statement at the end, how can I get the field values to the first procedure. In the following, I would want to pass the values from the results from procedure B to the declared values in procedure A
:CREATE PROCEDURE A
AS .....
DECLARE @FieldOne, @FieldTwo
exec B
CREATE PROCEDURE B
AS .....
SELECT FieldOne, FieldTwo FROM Table INTO #TEMP
SELECT * FROM #TEMP
RETURN
-
Why don't you simply select them (in Procedure A!) from the #temptable you have created? - or did you do that just for fun?
... as I'm looking at your code, I even doubt, that there's any need for procedure B. What are you trying to do?
_/_/_/ paramind _/_/_/ -
You have to create the temptable in the outer proc (A)
then you can call proc, have it insert into the temptable, return, and then you select from it again in the outer (A) procedure.
It won't work if you create the temptable in the called proc (B), since that temptable then is out of scope for the 'outer' proc (A)
example:
use northwind
go
create proc b
as
insert #x select orderid from orders
return
go
create proc a
as
create table #x (id int not null)
exec b
select * from #x
return
go
exec a
go
drop proc a, b
go
/Kenneth
-
Hey, thanks for the feedback. I've made the procedures simple for the explanation. Procedure B has functions that will be called by many stored procedures. You're saying that if I create a temp table in B, I can do a select * from that table in A. I will give this a try, Thanks!
Taffy
-
Ok, I see. Create the table in A then fill it is b. Thanks again guys!
Taffy
-
Kenneth, why should the ##temptable go out of scope? It's still a regular table. Of course, on more # is needed
_/_/_/ paramind _/_/_/ -
It might also be a possibility to replace procedure B with a function that returns a table. It depends on what you are doing in procedure B. If you e.g are updating another table or calling other stored procedures in procedure B, then it is not possible.
-
If it's ##temptable it won't go out of scope, but the example said #temptable, then it will.
One letter can make all the difference
/Kenneth
-
You can do this way also
CREATE PROCEDURE dbo.Works_Usp_Test
AS
SELECT
Emp_No
, Emp_Name
FROM Mst_Employee
GO
CREATE TABLE #Temp
(EMP_No INT
, Emp_Name Varchar(100))
INSERT INTO #Temp EXEC dbo.Works_Usp_Test
This way u can insert records in #Temp Table, but there should be only one resultset to be returned
Regards
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply