Execute Stored Procedures stored in table

Question

Hi Team - I have a main stored procedure that runs daily and this main procedure executes all the procedures in it. I have set up the main procedure in the SQL agent job. Below is how the main procedure is designed:
Create Procedure [dbo].[usp_MainProcedure]
AS begin
Exec dbo.usp_ChildProcedure_001
Exec dbo.usp_ChildProcedure_002
Exec dbo.usp_ChildProcedure_003
Exec dbo.usp_ChildProcedure_004
Exec dbo.usp_ChildProcedure_005
Exec dbo.usp_ChildProcedure_006
Exec dbo.usp_ChildProcedure_007
Exec dbo.usp_ChildProcedure_010
Exec dbo.usp_ChildProcedure_011
Exec dbo.usp_ChildProcedure_012
Exec dbo.usp_ChildProcedure_013
--There are baout 100 procedures

End

I have a table called letter:

LetterId ActiveInactiveInd
1 A
2 A
3 A
4 A
5 A
6 I
7 A
10 A
11 I
12 A
13 A
25 I

LetterId in the table represents the Id at the end of each Childprocedure ( Ex: Letterid =1 the child procedure is Exec dbo.usp_ChildProcedure_001).

This Main procedure has got 100 child procedures and will grow. Now I want to make this Main Store procedure as table-driven so it picks the child procedures from the table. I will create a third column called 'StoredProcforletter' in the letter table and insert the value' Exec dbo.usp_ChildProcedure_001' for the respective letter id.

Is it possible to make this table driven? I think we should write dynamic SQL? How do I write this process? I want to execute all the child procedures where ActiveInactiveInd = 'A'. Like below.

Create Procedure [dbo].[usp_MainProcedure]
AS begin
Exec 'Pick Stored Procedure from table table' where ActiveInactiveInd = 'A'

End

OR

declare @alenzi varchar(max)
select @alenzi = StoredProcforletter from letter where ActiveInactiveInd = 'A'
exec sp_executesql @alenzi ;

Answer 1

Hi @kkran ,

Please refer below and check whether it is helpful.

create table letter  
(  
LetterId int,  
ActiveInactiveInd varchar(10),  
StoredProcforletter varchar(100)  
)  
  
insert into letter values  
(1,'A','dbo.usp_ChildProcedure_001'),  
(2,'A','dbo.usp_ChildProcedure_002'),  
(3,'A','dbo.usp_ChildProcedure_003'),  
(4,'A','dbo.usp_ChildProcedure_004'),  
(5,'A','dbo.usp_ChildProcedure_005'),  
(6,'I','dbo.usp_ChildProcedure_006'),  
(7,'A','dbo.usp_ChildProcedure_007'),  
(10,'A','dbo.usp_ChildProcedure_010'),  
(11,'I','dbo.usp_ChildProcedure_011'),  
(12,'A','dbo.usp_ChildProcedure_012'),  
(13,'A','dbo.usp_ChildProcedure_013'),  
(25,'I','dbo.usp_ChildProcedure_025')  

SQL Server 2016 and before:

Create Procedure [dbo].[usp_MainProcedure]  
@ActiveInactiveInd varchar(10)  
AS   
begin  
declare @sql nvarchar(max)  
  
SELECT @sql=STRING_AGG(CONCAT('EXEC  ',StoredProcforletter ), CHAR(13))  
FROM letter  
where ActiveInactiveInd = @ActiveInactiveInd  
  
--print @sql  
EXECUTE sp_executesql @sql  
  
End  

exec [dbo].[usp_MainProcedure] 'A'  

SQL Server 2017 and after:

Create Procedure [dbo].[usp_MainProcedure]  
@ActiveInactiveInd varchar(10)  
AS   
begin  
declare @sql nvarchar(max)  
  
SELECT @sql=(  
             SELECT 'EXEC ' + StoredProcforletter +' '  
             FROM letter  
             FOR XML PATH(''))  
FROM letter  
where ActiveInactiveInd = @ActiveInactiveInd  
  
--print @sql  
EXECUTE sp_executesql @sql  
  
End  
exec [dbo].[usp_MainProcedure] 'A'  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

No, you don't need dynamic SQL. EXEC accepts a variable for the procedure name, so you can say

EXEC @spname @par1, @par2, ...

So all you need is to build @spname.