18 February 2010

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.

No comments:

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.