DECLARE @ProductIDs VARCHAR(MAX) ='1,2,14,16,19';
DECLARE @ProductNames VARCHAR(MAX) ='Tofu,Pavlova,Ikura';
DECLARE @ProductFamilyNames VARCHAR(MAX) = 'Beverages,Confections';
--
--$BeginRegion: DynamicSQL way of Writing the Code
DECLARE @SQLcmd NVARCHAR(MAX) =N'';
SET @SQLcmd= --
'SELECT PF.Product_Family_Desc, P.Prod_Id, P.Prod_Desc
FROM dbo.Products_Base AS P
INNER JOIN dbo.Product_Family AS PF ON PF.Product_Family_Id=P.Product_Family_Id
WHERE 1=1';
IF @ProductIDs IS NOT NULL
SET @SQLcmd = @SQLcmd + ' AND P.Prod_Id IN('+@ProductIDs+')'; --This Style Works if the CSV is based on Integers Only
IF @ProductNames IS NOT NULL
SET @SQLcmd = @SQLcmd + ' AND P.Prod_Desc IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ProductNames, '',''))'; --This Style Works for Any
IF @ProductFamilyNames IS NOT NULL
SET @SQLcmd = @SQLcmd + ' AND PF.Product_Family_Desc IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ProductFamilyNames, '',''))'; --This Style Works for Any
PRINT @SQLcmd;
EXECUTE sys.sp_executesql @SQLcmd, N'@ProductIDs NVARCHAR(MAX), @ProductNames VARCHAR(MAX), @ProductFamilyNames VARCHAR(MAX)', @ProductIDs, @ProductNames, @ProductFamilyNames;
--$EndRegion: DynamicSQL way of Writing the Code
--
--$BeginRegion: V1 - PlainSQL way of Writing the Code
SELECT PF.Product_Family_Desc, P.Prod_Id, P.Prod_Desc
FROM dbo.Products_Base AS P
INNER JOIN dbo.Product_Family AS PF ON PF.Product_Family_Id=P.Product_Family_Id
WHERE 1=1
AND (P.Prod_Id IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ProductIDs, ',')) OR @ProductIDs IS NULL)
AND (P.Prod_Desc IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ProductNames, ',')) OR @ProductNames IS NULL)
AND (PF.Product_Family_Desc IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ProductFamilyNames, ',')) OR @ProductFamilyNames IS NULL);
--$EndRegion: V1 - PlainSQL way of Writing the Code
--
--$BeginRegion: V2 - PlainSQL way of Writing the Code
SELECT PF.Product_Family_Desc, P.Prod_Id, P.Prod_Desc
FROM dbo.Products_Base AS P
INNER JOIN dbo.Product_Family AS PF ON PF.Product_Family_Id=P.Product_Family_Id
WHERE 1=1
AND CASE WHEN @ProductIDs IS NULL THEN 1 WHEN P.Prod_Id IN(SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ProductIDs, ','))AND @ProductIDs IS NOT NULL THEN 1 ELSE 0 END=1
AND CASE WHEN @ProductNames IS NULL THEN 1 WHEN P.Prod_Desc IN(SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ProductNames, ','))AND @ProductNames IS NOT NULL THEN 1 ELSE 0 END=1
AND CASE WHEN @ProductFamilyNames IS NULL THEN 1 WHEN PF.Product_Family_Desc IN(SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ProductFamilyNames, ','))AND @ProductFamilyNames IS NOT NULL THEN 1 ELSE 0 END=1;
--$EndRegion: V2 - PlainSQL way of Writing the Code
