Setting the order for executing the stored procedures within a master stored procedure

Question

I have a master stored procedure in which have many sub stored procedures, say 10 stored procedures.

What I want is if I specify that stored procedure 1, stored procedure 2, then only these 2 stored procedures should be executed, the other 8 should not be executed.

And if I don't specify any value for the ordering, then it should execute all the stored procedures.

Someone please tell me how to do it? Is there any way to implement it?

I need 1 parameter which could be comma separated. That is if we specify 1,2 in a parameter then it will execute stored procedures 1 and 2.

SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO
SET NOCOUNT ON;
GO


CREATE PROCEDURE dbo.CI_ILR_Indicator_Master
    (
     @StartYear INT,
     @EndYear INT
    )
AS
BEGIN

--Temptable '#TempILR' is created, which contains the common data needed for all the indicator calculation of 'ILR'


--Executing the SP [dbo.CI_ILR_Indicator_VRQ_WBL Indicators] for the ILR Indicator group 
--'ILR VRQ WBL' for Indicators (8,9,10)
EXEC SP1 @StartYear,@EndYear
EXEC SP2 @StartYear,@EndYear
EXEC SP3 @StartYear,@EndYear
EXEC SP4 @StartYear,@EndYear
EXEC SP5 @StartYear,@EndYear
EXEC SP6 @StartYear,@EndYear
EXEC SP7 @StartYear,@EndYear
EXEC SP8 @StartYear,@EndYear
EXEC SP9 @StartYear,@EndYear
EXEC SP10 @StartYear,@EndYear

END

This is how my SP looks..

Answer 1

CREATE PROCEDURE [dbo].[Master Sp]   
(  
     @INT_sp1 int= null,
     @INT_sp2 int= null,
     @INT_sp3 int= null,
     @INT_sp4 int= null,
     @INT_sp5 int= null,
     @INT_sp6 int= null,
     @INT_sp7 int= null,
     @INT_sp8 int= null,
     @INT_sp9 int= null,
     @INT_sp10 int= null
) 
AS  
BEGIN  
    SET NOCOUNT ON;

    if @INT_sp1 is not null
        exec dbo.sp1
    if @INT_sp2 is not null
        exec dbo.sp2
    ......
END

GO

Answer 2

Follow this stored Proc,it will helps you

CREATE PROCEDURE [dbo].[Usp_RunSp] --give sp list like 'Sp1,Sp2,Sp3' else by default
(
 @vr_SpList VARCHAR(1000)=NULL,
 @i_StartYear INT , 
 @i_EndYear INT 
) 
AS 
  BEGIN 
      BEGIN try 
          IF Object_id('tempdb..#FinalResult')IS NOT NULL 
            DROP TABLE #finalresult 

          IF Object_id('tempdb..#RunSp')IS NOT NULL 
            DROP TABLE #runsp 

          IF Object_id('tempdb..#TotalSPToRun')IS NOT NULL 
            DROP TABLE #totalsptorun 

          CREATE TABLE #finalresult 
            ( 
               Rno        INT, 
               Spnamelist VARCHAR(100) 
            ) 

          CREATE TABLE #totalsptorun 
            ( 
               Id         INT IDENTITY, 
               Spnamelist VARCHAR(100) 
            ) 

          DECLARE @RunSql NVARCHAR(max) 
          DECLARE @Sp_List TABLE 
            ( 
               Spnamelist VARCHAR(100) 
            ) 

          INSERT INTO @Sp_List 
          SELECT @vr_SpList 

          SELECT split.a.value('.', 'nvarchar(1000)') AS SpnameList 
          INTO   #runsp 
          FROM   (SELECT Cast('<S>' + Replace(Spnamelist, ',', '</S><S>') 
                              + '</S>' AS XML) AS Data 
                  FROM   @Sp_List) AS A 
                 CROSS apply data.nodes('S') AS Split(a) 

          INSERT INTO #totalsptorun --add your 10 sp names instead of sp1,sp2
                      (Spnamelist) 
          SELECT 'Sp1'           UNION ALL 
          SELECT 'Sp2'           UNION ALL 
          SELECT 'Sp3'           UNION ALL 
          SELECT 'Sp4'           UNION ALL 
          SELECT 'Sp5'           UNION ALL 
          SELECT 'Sp6'           UNION ALL 
          SELECT 'Sp7'           UNION ALL 
          SELECT 'Sp8'           UNION ALL 
          SELECT 'Sp9'           UNION ALL 
          SELECT 'S10' 

          IF @vr_SpList IS NOT NULL 
            BEGIN 
                SET @RunSql =' INSERT INTO #FinalResult 
                                SELECT  ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS RNo,
                                        R.SpnameList  
                                FROM #TotalSPToRun R  
                                    INNER JOIN  #RunSp L 
                                ON L.SpnameList=R.SpnameList' 

                EXEC(@RunSql) 
            END 

          IF @vr_SpList IS NULL 
            BEGIN 
                SET @RunSql =' INSERT INTO #FinalResult 
                                SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS RNo,
                                       R.SpnameList  
                                FROM #TotalSPToRun R ' 

                --PRINT @RunSql 

                EXEC(@RunSql) 
            END 

          DECLARE @SqlRun NVARCHAR(max), 
                  @SpName VARCHAR(100), 
                  @minID  INT, 
                  @maxId  INT 

          SELECT @minID = Min(Rno),@maxId = Max(Rno) 
          FROM   #Finalresult 

          WHILE ( @minID <= @maxId ) 
            BEGIN 
                SELECT @SpName = Spnamelist 
                FROM   #finalresult 
                WHERE  Rno = @minID 

                SET @SqlRun=N'EXECUTE ' + @SpName+' '+CAST(@i_StartYear  AS VARCHAR)+','+CAST(@i_EndYear  AS VARCHAR) 
                SET @minID=@minID + 1 

                PRINT @SqlRun 
                EXEC(@SqlRun) 
            END 
      END try 

      BEGIN catch 
          SELECT Error_number()    AS ErrorNumber, 
                 Error_severity()  AS ErrorSeverity, 
                 Error_state()     AS ErrorState, 
                 Error_procedure() AS ErrorProcedure, 
                 Error_line()      AS ErrorLine, 
                 Error_message()   AS ErrorMessage 
      END catch 
  END