Driving Stored Procedure using Driver Table in sql server 2016

Question

question

AlexKlap asked Oct 24, '20

Driving Stored Procedure using Driver Table in sql server 2016

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)

Kindly help if it seems realistic, as SelectionBIT is only available in driver table but not in data table.

t-sql sql2012

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-10-25T00:38:19Z

Magnus Ahlkvist answered Oct 25, '20

Sure, this is doable. The below will most probably produce correct results, but if you have many rows in the tables, especially in DriverTable, test it for performance please. I've created the tables as permanent user tables instead of table variables in my example.

CREATE OR ALTER PROC dbo.usp_testprod
AS
BEGIN
 WITH PredicateValues AS(
  SELECT
   CASE WHEN DT.CountryID = '' THEN NULL ELSE DT.CountryID END AS CountryID,
   CASE WHEN DT.PROD = '' THEN NULL ELSE DT.PROD END AS PROD,
   CAST(SS.value AS INT) AS CLAS
  FROM dbo.DriverTable AS DT
   OUTER APPLY STRING_SPLIT(dt.CLAS,',') AS SS
   WHERE DT.SelectionBIT=1
   AND DT.SP_Name=OBJECT_NAME(@@PROCID)
 )
 SELECT 
  DT.CountryID,
  DT.IID,
  DT.PROD,
  DT.Description,
  DT.CLAS
 FROM dbo.DataTable AS DT
 INNER JOIN PredicateValues AS PV
  ON DT.CountryID=COALESCE(PV.CountryID,DT.CountryID)
  AND DT.CLAS = COALESCE(PV.CLAS,DT.CLAS)
  AND DT.PROD = COALESCE(PV.PROD,DT.PROD)
END

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question