CODEWITHSUNDEEP
sqlsql-server-2012-express
Salwa Mirza
Salwa Mirza

Reputation: 1

how to call nested stored procedure sql

I'm trying to use the result of first stored procedure which gives me list of Proposals that are assigned to a particular expert. From that result I want the related projects of those proposals.

my first query is:

ALTER proc [dbo].[sp_ExpsAssignedProp]
(  
@username as varchar(50)  
)  
as  
begin  
select pro.ProposalID,pro.Title,pro.GrantAmount from tbl_Registration reg  
inner join tbl_Expert exp On reg.RegID=exp.RegID  
inner join tbl_Panel pan On pan.ExpertID_1=exp.ExpertID   
OR pan.ExpertID_2=exp.ExpertID  
OR pan.ExpertID_3=exp.ExpertID  
inner join tbl_Association asso On asso.PanelID=pan.PanelID  
inner join tbl_Proposal pro On asso.ProposalID=pro.ProposalID  
where reg.Username=@username  
end

now I'm tryin to use its result to the second query but its giving me all the projects with status as finished. My concern is get the assigned proposal's projects with status as finished.

alter proc [dbo].sp_AssignedProj  
(  
@username as varchar(50),  
@status as varchar(50)  
)  
as  
begin  
exec sp_ExpsAssignedProp   
select proj.ProjectID, proj.Title, proj.Budget, proj.StartDate, proj.FinishDate,    proj.CurrentStatus from  
tbl_Proposal prop   
inner join tbl_Project proj On prop.ProposalID=proj.ProposalID  
where CurrentStatus=@status  
end

Upvotes: 0

Views: 9308

Answers (2)

jpw
jpw

Reputation: 44911

You can share data between the procedures by using temporary (or permanent) tables.

In the first procedure you can set up a temporary table like:

CREATE TABLE #TempTableName
(
    <table definition that matches what's being inserted here>
)

And then insert whatever you need into it:

INSERT #TempTableName EXEC myStoredProc

And in the second procedure you can use the data and then remove the table:

SELECT * FROM #TempTableName (or JOIN or whatever).
DROP TABLE #TempTableName

Upvotes: 1

Luis LL
Luis LL

Reputation: 2993

When you exec one procedure from another, you don't get the result set in the caller SP, the result set goes to the caller. If you need to use the result set you use 

INSERT INTO XXX
EXEC [sp name]

Upvotes: 0

Related Questions