how to call nested stored procedure sql

Question

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

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, Jul 06 '13 at 20:15
Side note (assuming you're using **SQL Server**): you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jul 06 '13 at 20:15
@marc_s Thanks. I did not know about naming stored procedures. — Salwa Mirza, Jul 07 '13 at 08:21
https://stackoverflow.com/questions/20105443/how-to-pass-a-temp-table-as-a-parameter-into-a-separate-stored-procedure/20105766#20105766 this may be a repeat question imho. but i'll let you decide to leave it up or not. — granadaCoder, May 16 '19 at 21:37

score 1 · Answer 1 · answered Jul 06 '13 at 13:21

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

score 0 · Answer 2 · answered Jul 06 '13 at 13:10

0

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]

answered Jul 06 '13 at 13:10

Luis LL

2,912
2
19
21

how to call nested stored procedure sql

2 Answers2