Driving Stored Procedure using Driver Table in sql server 2016

Question

Hello,

My MasterData table looks like:-

DECLARE @DATATABLE TABLE

(
CountryID INT,
IID VARCHAR(10),
PROD VARCHAR (50),
Description  VARCHAR (250),
CLAS INT

)

INSERT @DATATABLE

SELECT 12,'IM','S350A','LCKNG NAT',04 UNION ALL
SELECT 45,'RD','16M120','FLAT WASHER',04 UNION ALL
SELECT 12,'IM','A14751','CNCTR2.5MM',01 UNION ALL
SELECT 12,'IM','X15129','OIL LEVEL',01 UNION ALL
SELECT 69,'XY','5VB554','SOLID',14 UNION ALL
SELECT 71,'NS','1Q5128','EVELLI',14 UNION ALL
SELECT 71,'YT','PM2001','MINI31',01

SELECT * FROM @DATATABLE

And The Driver table feilds looks like:-

DECLARE @DRIVERTABLE TABLE
(
SP_Name VARCHAR(50),
PROD VARCHAR(50),
CountryID INT,
CLAS VARCHAR(30),
SelectionBIT INT

)

INSERT @DRIVERTABLE

SELECT 'usp_testprod','','12','04,01','1' union all
SELECT 'usp_testprod','','12','04'   ,'0' union all
SELECT 'usp_testprod','','12','09'    ,'0' union all
SELECT 'usp_testprod','','31','04'    ,'0' union all

SELECT 'usp_testcust','A14751','65','','1' union all
SELECT 'usp_testcust','PM2001','39','','0' 

I am trying to develop stored procedure which return data from Master Data table using parameter from driver table.

when SP Name match and ****SelectionBIT** = 1** then it will use feilds from driver table which are not null or not blank to pass as input parameter to get output.

Expected Output of SP usp_testprod is:

12 IM S350A LCKNG NAT 4
12 IM A14751 CNCTR2.5MM 1
12 IM X15129 OIL LEVEL 1

as country id = 12 and iclas in (1,4)

Please help.

Answer 1

It seems that you already have an answer, which can be adjusted if required: https://learn.microsoft.com/en-us/answers/questions/137723/execute-sp-whose-input-parameter-value-is-coming-f.html. You can also adjust the invoked stored procedures to deal with empty and null parameters. The SelectionBIT column can be filtered by cursor and does not have to be passed to procedures.

But if you are actually on redesign phase, maybe you need something like this:

declare @DATATABLE as table  
(  
    CountryID INT,  
    IID VARCHAR(10),  
    PROD VARCHAR (50),  
    Description  VARCHAR (250),  
    CLAS INT  
)  
      
INSERT @DATATABLE  
SELECT 12,'IM','S350A','LCKNG NAT',04 UNION ALL  
SELECT 45,'RD','16M120','FLAT WASHER',04 UNION ALL  
SELECT 12,'IM','A14751','CNCTR2.5MM',01 UNION ALL  
SELECT 12,'IM','X15129','OIL LEVEL',01 UNION ALL  
SELECT 69,'XY','5VB554','SOLID',14 UNION ALL  
SELECT 71,'NS','1Q5128','EVELLI',14 UNION ALL  
SELECT 71,'YT','PM2001','MINI31',01  
      
declare @DRIVERTABLE as table  
(  
    SP_Name VARCHAR(50),  
    PROD VARCHAR(50),  
    CountryID INT,  
    CLAS VARCHAR(30),  
    SelectionBIT INT  
)  
  
INSERT @DRIVERTABLE  
SELECT 'usp_testprod','','12','04,01','1' union all  
SELECT 'usp_testprod','','12','04' ,'0' union all  
SELECT 'usp_testprod','','12','09' ,'0' union all  
SELECT 'usp_testprod','','31','04' ,'0' union all  
SELECT 'usp_testcust','A14751','65','','1' union all  
SELECT 'usp_testcust','PM2001','39','','0'  
  
  
SELECT * FROM @DATATABLE  
SELECT * FROM @DRIVERTABLE  
  
---  
  
select distinct d.*  
from @DATATABLE as d  
cross join @DRIVERTABLE as f   
where f.SelectionBIT = 1  
and (nullif(f.CountryID, 0) is null or d.CountryID = f.CountryID)  
and (nullif(f.PROD, '') is null or d.PROD in (select ltrim(value) from STRING_SPLIT(f.PROD, ',')))  
and (nullif(f.CLAS, '') is null or d.CLAS in (select ltrim(value) from STRING_SPLIT(f.CLAS, ',')))  

This does not require separate stored procedures and it gives a single output.

Answer 2

If you pass PROD, CountryID and CLAS, but PROD is not used, then consider this stored procedure:

create procedure usp_testprod
    @unused_prod varchar(max),
    @countryId int,
    @classes varchar(max)
as begin

    select * 
    from DATATABLE
    where CountryID = @countryId
    and CLAS in (select value from string_split(@classes, ','))

end

Since it is problematic to access @DATATABLE variable, it assumes that the real code uses regular DATATABLE table.

Do you also want to check optionally the PROD parameter? Is it a single name or a ','-separated list?

Answer 3

This seems to be the same question that you posted earlier and you accepted my answer. If that answer after all was not what you were looking for, I think it would have been better to stay in the same thread, and unmark my post as the answer.

I am not entirely clear what you are looking for. But if you wonder how to implement this usp_testprod, the pattern for that type of procedure is:

WHERE (col1 = @par1 OR @par1 IS NULL)
     (col2 = @par2 OR @par2 IS NULL)
    ...
OPTION (RECOMPILE)

The hint RECOMPILE is needed because for this type of query you want different indexes to be used depending on the conditions you send in.

For multi-valued conditions, I recommend that you use string_split (see Viorel's post) or similar functions to transform the list to table format and insert the values into temp tables, using this pattern:

 (col3 IN (SELECT val FROM #par3temp) OR @par3 IS NULL)

Again, this is for performance reasons.