SQL Reloaded: Pulling the Strings of SQL Server X (Dynamic Queries)

    A dynamic query is a query constructed at runtime, techniques often indispensable in many situations that require a certain flexibility in query’s creation. The creation of a dynamic query is nothing but a set of operations with strings, many of the techniques mentioned before becoming handy. SQL Server provides two functions for the execution of dynamic queries, namely EXECUTE statements (or its shortened form EXEC) and sp_executesql stored procedure. Even if the later it’s more flexible allowing passing parameters from and to the caller and allows reusing executions plans (see Using sp_esecutesql), for the following examples will be used only EXEC. But before let’s look how a static could become dynamic. For this let’s consider the following query based on AdventureWorks database:
 

-- example 1 - simple query   
SELECT *  
FROM Person.Address  
WHERE AddressID = 1 

-- example 2 - query encapsulated in a string: 
EXEC ('SELECT * FROM Person.Address WHERE AddressID = 1') 

-- example 3 - query stored into a string variable      
EXEC ('SELECT * FROM Person.Address WHERE AddressID = 1') 

    Supposing that the AddressID is considered as parameter we can write:

-- example 4 - static query     
DECLARE @AddressID int 
SET @AddressID = 1 
SELECT *  
FROM Person.Address  
WHERE AddressID = @AddressID  

-- example 5 - dynamic query  
DECLARE @sql varchar(100) 
DECLARE @AddressID int 
SET @AddressID = 1 
SET @sql = 'SELECT * FROM Person.Address WHERE AddressID = ' + CAST(@AddressID as varchar (10)) 
EXEC (@sql) 

   Until here there is no important conceptual difference. What if is needed to pass multiple AddressIDs? We can create a parameter for which expected values, though that’s not a reasonable solution as the number of values can vary. A more elegant solution would be to create a list of values and provided as a string parameter and then concatenate the original query and the string parameter like below. We just need to accommodate the length of the string variable to the expected size of the list of value.
 

-- example 6 (dynamic query) 
DECLARE @sql varchar(100) 
DECLARE @AddressIDs varchar(50) -- supposed parameter 
SET @AddressIDs = '1, 2, 4, 5, 6, 10'  
SET @sql = 'SELECT * FROM Person.Address WHERE AddressID IN (' + @AddressIDs + ')' 
EXEC (@sql) 

    There is actually a third solution. As in the previous post on list of values has been introduced the dbo.StringToTable function, the function can be used thus to transform the list in a table:
 

-- example 7 (list of values) 
DECLARE @AddressIDs varchar(50) -- supposed parameter 
SET @AddressIDs = '1,2,4,5,6,10'  
SELECT *  
FROM Person.Address  
WHERE AddressID IN ( 
      SELECT value  
      FROM dbo.StringToTable(@AddressIDs, ',')) 

    In the same post was constructed the DoubleList list of values which can be used in a dynamic query in bulk inserts or table-value constructors. The list needs to be slightly modified by replacing the single quote with two single quotes in order to accommodate value’s storage in a string. Considering that there are no integrity constraints on the targeted table, he query for bulk insert can be written as follows:
 

-- example 8 (list of values & bulk insert) 
DECLARE @sql varchar(200) 
DECLARE @AddressTypes varchar(150)  
SET @AddressTypes = '(6,''Archive''), (1,''Billing''), (2,''Home''), (3,''Main Office''), (4,''Primary''), (5,''Shipping'')' 
SET @sql = 'INSERT Person.AddressType (AddressTypeID, Name) VALUES ' + @AddressTypes  
EXEC (@sql) 

    The same technique can be used with a table-value constructor:
 

-- example 9 (list of values & table-value constructor) 
DECLARE @sql varchar(400) 
DECLARE @AddressTypes varchar(150)  
SET @AddressTypes = '(6,''Archive''), (1,''Billing''), (2,''Home''), (3,''Main Office''), (4,''Primary''), (5,''Shipping'')' 
SET @sql = 'SELECT VA.AddressID, VA.AddressTypeID, AT.NAME FROM Purchasing.VendorAddress VA JOIN ( VALUES ' + @AddressTypes + ') AS AT(AddressTypeID, Name) ON VA.AddressTypeID = AT.AddressTypeID' 
EXEC (@sql) 

    The above examples are basic, in daily problems such queries can involve multiple parameters and operations. In addition, in the last examples the concatenation step was left out.

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.