Execute and return value of SP within another SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Execute and return value of SP within another SP

Author

Topic

boblarson
Starting Member

17 Posts

Posted - 2008-05-07 : 16:45:44

I need to get a value from another Stored Procedure to use within another Stored Procedure. This is what I currently have, but it is not even close, I'm sure:


CREATE PROCEDURE dbo.sp_JT_BS01c_Calendar_Build_BufferSheet_DateRange  AS
 
DECLARE @MinOfDMD_WK datetime, @MaxOfDMD_WK datetime 
 
@MinOfDMD_WK = Exec sp_JT_BS01a_CalendarBuild_Min_PlanningWeek
@MaxOfDMD_WK = Exec sp_JT_BS01b_CalendarBuild_Max_PlanningWeek
 
SELECT @MinOfDMD_WK, [master - weekly_range].week, 
 
@MaxOfDMD_WK, dbo.fn_Bucket_Range([week],@MinOfDMD_WK,@MaxOfDMD_WK) AS [Inclusion Range]
 
FROM [master - weekly_range], @MaxOfDMD_WK, @MinOfDMD_WK
 
WHERE dbo.fnBucket_Range([week],@MinOfDMD_WK,@MaxOfDMD_WK)=1;

Thanks,

Bob Larson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 16:50:43

Use OUTPUT parameters in sp_JT_BS01a_CalendarBuild_Min_PlanningWeek and sp_JT_BS01b_CalendarBuild_Max_PlanningWeek.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-08 : 01:22:33

Yes tkizer is right, output parameters in the inner sp's will solve your problem.

You can create one of the sp as follows:

create proc sp_JT_BS01a_CalendarBuild_Min_PlanningWeek
(
@MinOfDMD_WK datetime output
)
as

select @MinOfDMD_WK = GETDATE()

go

And then you can call this sp in the main one as shown below

declare @MinOfDMD_WK datetime
Exec sp_JT_BS01a_CalendarBuild_Min_PlanningWeek @MinOfDMD_WK out
select @MinOfDMD_WK

I hope it helps,

Eralper
http://www.kodyaz.com/content/SQLServerArticles.aspx

-------------
Eralper
http://www.kodyaz.com

boblarson
Starting Member

17 Posts

Posted - 2008-05-08 : 01:30:17

Eralper -

Your answer helped IMMENSELY! I just needed to see how it is done once and now I will be able to use this from now on if I ned to do it again. I had been trying this all day trying to find out how to use the output parameters because I couldn't find an example to show me how to do it and I just could never get the syntax just right. So, thank you, thank you, thank you!

Thanks,

Bob Larson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources