Execute SP whose input parameter value is coming from Driver table in sql 2016

Question

Hello ,

i am trying to get re-usability from a SP.

i have a driver table which contain the sp name and other input parameter.DDL looks like below.

DECLARE @DRIVERTABLE TABLE
 (
 SP_Name VARCHAR(50),
 Country VARCHAR(50),
 SELLCLAS VARCHAR(50),
 SELLID VARCHAR(50),
 CustomerNo VARCHAR(50),
 SelectionBIT INT

 )

 INSERT @DRIVERTABLE

 SELECT 'usp_ProdSell','Spain','1,2','SPM','IR90', '1' UNION ALL
 SELECT 'usp_Customerlist','Germany','9,3','GMBH','GM210','1' UNION ALL
 SELECT 'usp_ProdSell','India','8,7','IND',NULL,'1' UNION ALL
 SELECT 'usp_ProdSell','Germany','11','GMBH','', '1' UNION ALL
 SELECT 'usp_Customerlist','Mexico',NULL,'MEXI','MX37','1' 

The Master SP looks like below

USE [MyDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_ProdSell]
AS
BEGIN

SET NOCOUNT ON;

SELECT DISTINCT * INTO TEMPA FROM tablea AS a LEFT OUTER JOIN [dbo].[tableb] AS b 
ON a.keycol = b.keycol
WHERE SELLCLAS in (SELECT SELLCLAS FROM @DRIVERTABLE)
AND SELLID IN (SELECT SELLID FROM @DRIVERTABLE)
AND CustomerNo IN (SELECT CustomerNo FROM @DRIVERTABLE)
AND SelectionBIT = 1

Is it possible to modify sp so it takes the input parameter from Driver Table
and execute only when SelectionBit Is 1

For example

i want to execute SP_Name "usp_ProdSell" for country Spain, just i need to pass selectionbit = 1
in Exec usp_ProdSell and it will takes parameter SELLCLAS,SELLID and CustomerNo from Driver table
and gives the output

How can i pass parameter from driver table and push the output of SP to table.

Please help me.

Answer 1

This presumes that all procedures have the same parameter profile:

 DECLARE @DRIVERTABLE TABLE
     (
     SP_Name VARCHAR(50),
     Country VARCHAR(50),
     SELLCLAS VARCHAR(50),
     SELLID VARCHAR(50),
     CustomerNo VARCHAR(50),
     SelectionBIT INT

     )

DECLARE @cur CURSOR,
        @SP  sysname,
        @Country varchar(50),
        @SELLCLAS varchar(50),
        @SELLID    varchar(50),
        @CustomerNo varchar(50),
        @selectionBIT int

SET @cur = CURSOR STATIC FOR 
           SELECT SP_Name, Country, SELLCLAS, SELLID,
                  CustomerNo, SelectionBIT
           FROM   @DRIVERTABLE

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @SP, @Country, @SELLCLAS, @SELLID,
                   @CustomerNo, @selectionBIT
   IF @@fetch_status <> 0
      BREAK

   EXEC @SP @Country, @SELLCLAS, @SELLID,
            @CustomerNo, @selectionBIT
END