Executing multiple Procedures inside a Procedure

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

Executing multiple Procedures inside a Procedure

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-10-06 : 16:02:25

I have a procedure that has variables. I need it to execute 8 other procedures using the same variables. I get errors about batches and not using EXEC correctly. How do I do this? This is what I have so far:


Create PROCEDURE [dbo].[sel_hrcm_data_Module] (@subsidiaryid smallint, @month smallint, @year smallint) AS
--Debug
--DECLARE @subsidiaryid smallint
--SET @subsidiaryid = 159
--DECLARE @month smallint
--SET @month = 5
--DECLARE @year smallint
--SET @year = 2010
--Main
EXEC sel_hrcm_data_Emp_Prod_Graph @subsidiaryid, @month, @year 
GO
EXEC sel_hrcm_data_ESat_Table @subsidiaryid, @month, @year 
GO
EXEC sel_hrcm_data_Attrition_Graph @subsidiaryid, @month, @year 
GO
EXEC sel_hrcm_data_Absenteeism @subsidiaryid, @month, @year 
GO
EXEC sel_hrcm_data_Floor_Headcount @subsidiaryid, @month, @year 
GO
EXEC sel_hrcm_data_Recruitment_And_Selection @subsidiaryid, @month, @year 
GO
EXEC sel_hrcm_data_Recruitment_Effort @subsidiaryid, @month, @year 
GO
EXEC sel_hrcm_data_Job_Applications @subsidiaryid, @month, @year 
GO
sel_hrcm_data_Training_Wkst_Occupation @subsidiaryid, @month, @year 
GO
sel_hrcm_data_Training @subsidiaryid, @month, @year 
GO

Craig Greenwood

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-06 : 16:05:19

Nice design :)
Remove all the "GO"s. You can't have a "GO" inside an SP.

Be One with the Optimizer
TG

craigwg
Posting Yak Master

154 Posts

Posted - 2010-10-06 : 16:46:53

I read up and learned about GOs. I also learned that I needed to wrap this in a transaction (Begin Transaction EXEC SP1, EXEC SP2...COMMIT). Down hill from there.

Thanks team!

Craig Greenwood

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-06 : 16:51:24

Good - I hear reading is FUNdamental :)

Strictly speaking you don't need a transaction. Perhaps your business rules require it. You should read up on transaction next.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources