SQL Reloaded: Just in CASE V: (Dynamic Queries)

    In the previous post on the same topic: Just in CASE - Part V: Dynamic queries I shown how the dynamic queries built inside of a stored procedure could be used in order to handle multiple parameters requests combinations. Such technique could be useful also when dealing with more example queries, for example when needed to show the same data but at different levels of detail, or when needed to join different database objects (tables, views, table-valued functions) based on given criteria.
-- Retrieve aggregated POs at different levels of detail (1 - Vendor, 2 - Product, 3 - Purchase Order) CREATE PROCEDURE dbo.pGetAggregatedPOs(
@LevelOfDetail smallint = 0)
AS BEGIN DECLARE @Query varchar(2000)
DECLARE @GroupBy varchar(200)
-- Building GROUP BY clause SET @GroupBy = CASE
    WHEN @LevelOfDetail IN (1, 3) THEN ', POD.VendorNumber, POD.Vendor, POD.VendorActiveFlag'
    ELSE ''
END
+ CASE
    WHEN @LevelOfDetail = 2 THEN ', ITM.ProductName, ITM.ProductNumber, ITM.ProductModel'
     ELSE ''
END
+ CASE
    WHEN @LevelOfDetail = 3 THEN ', POD.PurchaseOrder'
    ELSE ''
END
IF LEN(@GroupBy)>0
    SET @GroupBy = RIGHT(@GroupBy, Len(@GroupBy)-1)
ELSE
    SET @GroupBy = ' POD.VendorNumber, POD.Vendor, POD.VendorActiveFlag, ITM.ProductName, ITM.ProductNumber, ITM.ProductModel, POD.PurchaseOrder'
SET @Query = 'SELECT '
+ @GroupBy
+ ', SUM(POD.OrderQty) TotalOrderQty'
+ ', SUM(POD.ReceivedQty) TotalReceivedQty'
+ ', SUM(POD.RejectedQty) TotalRejectedQty'
+ ', SUM(POD.OpenQty) TotalOpenQty'
+ ' FROM Purchasing.vPurchaseOrderDetailEx POD'
+ ' JOIN Production.vProducts ITM ON POD.ProductID = ITM.ProductID'
+ ' GROUP BY '
+ @GroupBy
+ ' ORDER BY '
+ @GroupBy EXEC(@Query)
END    In the above stored procedures for @LevelOfDetail parameter should be provided 1 in case is needed to aggregated the data at Vendor, 2 for Product level, and 3 at Purchase Order. In case no parameter is provided or it has other value than 1, 2 or 3, the result will be aggregated at Vendor, Product, Purchase Order combined. Here are the test cases for each case:
EXEC dbo.pGetAggregatedPOs 1 -- Vendors test
EXEC dbo.pGetAggregatedPOs 2 -- Products test
EXEC dbo.pGetAggregatedPOs 3 -- Purchase Orders test
EXEC dbo.pGetAggregatedPOs -- no parameters test
Note:
     You should always treat also the case in which the user provides other value for the parameter than the expected list of values.
     The above stored procedure could be modified in order to provide also the current On Hand in case the User has chosen to aggregate the records at Product level, the easiest way to achieve this is to encapsulate the logic in a view or table-valued function and join it to the main query, adding the OnHand in the GROUP BY clause.

    In the previous post I shown also how a dynamic query could be rewritten using specific techniques for dates, single attributes and list of values. Even the respective approach could affect the performance of the query it allows to encapsulate the same logic also in a table-valued function, making possible to reuse the functionality in other queries and even apply other types of constraints not already handled by the parameters in scope.
-- Retrieve Products using table-valued function CREATE FUNCTION dbo.fSearchProducts(
@ProductID int
, @ProductSubcategoryID int
, @ProductModelIDs varchar(500)
, @ProductName nvarchar(50)
, @ProductNumber nvarchar(25)
, @MakeFlag bit
, @FinishedGoodsFlag bit
, @StartStandardCost money
, @EndStandardCost money
, @StartSellStartDate varchar(10)
, @EndSellStartDate varchar(10))
RETURNS TABLE
AS
RETURN (
SELECT ProductID, ProductName, ProductNumber, ProductModel, CatalogDescription, ProductSubcategory
, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice
, Size, SizeUnitMeasureCode, SizeUnitMeasure, WeightUnitMeasureCode, WeightUnitMeasure, Weight
, DaysToManufacture, ProductLine, Class, Style, dbo.GetDateAsString(SellStartDate) SellStartDate
, dbo.GetDateAsString(SellEndDate) SellEndDate, dbo.GetDateAsString(ModifiedDate) ModifiedDate
FROM Production.vProducts
WHERE IsNull(ProductID, -1) = COALESCE(NullIf(@ProductID, 0), ProductID, -1)
AND IsNull(ProductSubcategoryID, -1) = COALESCE(NullIf(@ProductSubcategoryID, 0), ProductSubcategoryID, -1)
AND IsNull(ProductModelID, -1) IN (SELECT value FROM dbo.SplitList(IsNull(NullIf(@ProductModelIDs, ''), CAST(IsNull(ProductModelID, -1) as varchar(20)) ), ','))
AND IsNull(ProductName, ' ') LIKE IsNull(NullIf(@ProductName, ''), IsNull(ProductName, ' '))
AND IsNull(ProductNumber, ' ') LIKE IsNull(NullIf(@ProductNumber, ''), IsNull(ProductNumber, ' '))
AND IsNull(MakeFlag, 0) = COALESCE(NullIf(@MakeFlag, 0), MakeFlag, 0)
AND IsNull(FinishedGoodsFlag, 0) = COALESCE(NullIf(@FinishedGoodsFlag, 0), FinishedGoodsFlag, 0)
AND StandardCost BETWEEN ISNULL(NullIf(@StartStandardCost, 0), StandardCost) AND ISNULL(NullIf(@EndStandardCost, 0), StandardCost)
AND DateDiff(d, IsNull(SellStartDate, '1753-01-01'), dbo.GetStringDate(IsNull(NullIf(@StartSellStartDate, ''), '01-01-1753')))<=0
AND DateDiff(d, IsNull(SellStartDate, '1753-01-01'), dbo.GetStringDate(IsNull(NullIf(@EndSellStartDate, ''), '31-12-2009')))>=0
)      Here are the rewritten test cases for the tabled-defined functions, the output being the same:
SELECT *
FROM dbo.fSearchProducts(0, 0, '', '', '', 0, 0, 0, 0, '', '') -- all records
SELECT *
FROM dbo.fSearchProducts(1, 0, '', '', '', 0, 0, 0, 0, '', '') -- ProductID test
SELECT *
FROM dbo.fSearchProducts(0, 2, '', '', '', 0, 0, 0, 0, '', '') -- ProductSubcategoryID test
SELECT *
FROM dbo.fSearchProducts(0, 0, '3, 4, 5', '', '', 0, 0, 0, 0, '', '') -- ProductModelIDs test
SELECT *
FROM dbo.fSearchProducts(0, 0, '', 'Mountain%', '', 0, 0, 0, 0, '', '') -- ProductName test
SELECT * FROM dbo.fSearchProducts(0, 0, '', '', 'BK-T18U%', 0, 0, 0, 0, '', '') -- ProductNumber test
SELECT *
FROM dbo.fSearchProducts(0, 0, '', '', '', 1, 1, 0, 0, '', '') -- MakeFlag & FinishedGoodsFlag test
SELECT *
FROM dbo.fSearchProducts(0, 0, '', '', '', 0, 0, 50, 110, '', '') -- StandardCost test
SELECT *
FROM dbo.fSearchProducts(0, 0, '', '', '', 0, 0, 0, 0, '01/01/2001', '31/12/2002') -- SellStartDate test
    Here’s another example in which several other constraints are used with the table-valued function:
SELECT *
FROM dbo.fSearchProducts(0, 0, '3, 4, 5', '', '', 0, 0, 0, 0, '01/01/2001', '31/12/2002')
WHERE Color = 'Black'
AND Size BETWEEN '32' AND '44'
ORDER BY ProductNumber

     In such queries should be targeted to use function’s parameters rather than using the same parameters in the WHERE constraint of a query! There could be also exceptions in which complex constraints based on multiple attributes need to appear in WHERE clause.