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: Pulling the Strings of SQL Server VII (List of Values)

Introduction

    Lists are one of the basic structures in Mathematics, the term referring to an (ordered) set of elements separated by comma, space or any other delimiter (e.g. “:”, “;”). The elements of a list can be numbers, words, functions, or any other type of objects. In the world of databases, a list is typically formed out of the values of a given column or a given record, however it could span also a combination of rows and records, is such cases two delimiters being needed – one for column and one for row. From here comes probably the denomination of list of values. In a more general accept a list of values could be regarded as a delimited/concatenated subset. Such lists are formed when needed to send the data between the layers of an application or applications, this type of encoding being quite natural. In fact, also the data in a database are stored in similar tabular delimited structure, more complex though.  

    An useful example in which the list of values are quite handy is the passing of multiple values within the parameter of stored procedure or function (see example). This supposes first building the list and then use the values in a dynamic build query (like in the before mentioned example) or by building a table on the fly. We can call the two operations composition, respectively decomposition of list of values.

Composition 

Composition, whether on vertical or horizontal is nothing but a concatenation in which the values alternate with one or more delimiters. Let’s reconsider the concatenation based on the values of a Person.AddressType AdventureWorks table. As the logic for concatenating for one or more attributes is the same, the below example concatenates a list based on a single attribute, namely AddressTypeID in SingleList, respectively two attributes, AddressTypeID and Name.

-- concatenation of values across a table 
;WITH CTE (AddressTypeID, Name, Ranking) 
AS (--preparing the data       
     SELECT AddressTypeID  
     , Name 
     , ROW_NUMBER () OVER(ORDER BY Name) Ranking 
     FROM Person.AddressType 
     -- WHERE ... 
) 
, DAT (SingleList, DoubleList, Ranking) 
AS ( -- concatenating the values 
     SELECT Cast(AddressTypeID as varchar(max)) SingleList 
     , Cast('('+ Cast(AddressTypeID as varchar(10)) + ',''' + Name + ''')' as varchar(max)) DoubleList 
     , Ranking 
     FROM CTE 
     WHERE Ranking = 1 
     UNION ALL 
     SELECT DAT.SingleList + ',' + Cast(CTE.AddressTypeID as varchar(20)) SingleList 
    , Cast(DAT.DoubleList + ', ('+ Cast(CTE.AddressTypeID as varchar(10)) + ',''' + CTE.Name + ''')' as varchar(max)) DoubleList 
    , CTE.Ranking  
     FROM CTE          
       JOIN DAT           
          ON CTE.Ranking = DAT.Ranking + 1       
)  

-- the lists 
SELECT SingleList 
, DoubleList 
FROM DAT 
WHERE Ranking = (SELECT MAX(Ranking) FROM DAT) 

 
 
   The second example is based on atypical delimiters, resembling to the structure built for a batch insert or table value constructor-based statement, and as we’ll see later, ideal to be used in a dynamically-built query

Decomposition

Decomposition follows the inverse path, though it’s much easier to exemplify. In fact it’s used the same technique introduced in the last example from the previous post belonging to the same cycle, Subparts of a String, in which a space was used as delimiter. Another example is the dbo.SplitList function which decomposes a string using a loop.

-- decomposition of a string to a table using CTE 
CREATE FUNCTION dbo.StringToTable( 
 @str varchar(500) 
,@Delimiter char(1)) 
RETURNS @Temp TABLE ( 
Id int NOT NULL 
,Value varchar(50)) 
AS 
BEGIN  
     ;WITH CTE (PrevString, Position, Word)  
     AS (  
     SELECT LTrim(RTrim( CASE  
           WHEN CharIndex(@Delimiter, @str)>;0 THEN Right(@str, Len(@str)-CharIndex(@Delimiter, @str))  
           ELSE ''  
      END)) PrevString  
     , 1 Position  
     , LTrim(RTrim(CASE  
           WHEN CharIndex(@Delimiter, @str)>0 THEN LEFT(@str, CharIndex(@Delimiter, @str)-1)  
           ELSE @str  
       END)) Word  
      UNION ALL  
      SELECT LTrim(RTrim(CASE  
            WHEN CharIndex(@Delimiter, PrevString)>0 THEN Right(PrevString, Len(PrevString)-CharIndex(@Delimiter, PrevString))  
             ELSE ''  
       END)) PrevString  
      , Position + 1 Position  
      , LTrim(RTrim(CASE  
           WHEN CharIndex(@Delimiter, PrevString)>0 THEN LEFT(PrevString, CharIndex(@Delimiter, PrevString)-1)  
          ELSE PrevString  
      END)) Word      FROM CTE  
     WHERE Len(PrevString)>0  
    )  
     INSERT @Temp(Id, Value) 
     SELECT Position  
     , Word      FROM CTE  
     OPTION (maxrecursion 100)  
     RETURN 
END    

Here are two examples based on the single list created above and another one based on alphabet:

-- decomposing a list
SELECT Id 
, value 
FROM dbo.StringToTable('6,1,2,3,4,5', ',')     


-- decomposing the "alphabet" 
SELECT Id 
, value 
FROM dbo.StringToTable('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z', ',') 

    


   
Even if the function deals only with a delimiter, it could be used to decompose lists involving multiple delimiters, as long the list is adequately built:

-- decomposing double list 
SELECT Id 
, value 
, Left(value, CHARINDEX(',', value)-1) LeftValue 
, Right(value, len(value)-CHARINDEX(',', value)) RightValue 
FROM dbo.StringToTable('6,Archive;1,Billing;2,Home;3,Main Office;4,Primary;5,Shipping', ';')     

 
 The tables built thus from list of values can be further used in queries when needed to create a table on the fly. It would be interesting maybe to show that the composition and decomposition are inverse functions, however that’s out of scope, at least for current set of posts. 

SQL Reloaded: Table Value Constructors at Work

    Paradoxically, the first new SQL Server 2008 feature I learned, soon after the first CTP was made available, was the use of table value constructors, however I managed all this time without using it. This happened because I was either working on previous SQL Server versions or because other techniques were more appropriate to the problems I had to solve. So, a few days ago, the use of table value constructors came again on the table when I was weighting what’s the best approach to a problem posed by a colleague. Actually, the problem is quite simple – given a pair of values (id, new value) is needed to update a table with the “new value” corresponding to the given “id”. Previously, in similar cases, I preferred to load the data in a staging table because this was allowing me to quickly perform several checks on the set of values (e.g. check for duplicates, identify how many records will be updated). But what do you do when you are working directly in a production or any other type of environment in which you don’t have the possibility of using a staging table?

    For such cases you can either create a dataset on the fly with a set of UNIONS and reuse it in queries or, create an UPDATE statement for each pair of values. You can even automate the creation of UNION and UPDATE statements, however their use doesn’t seem so elegant, especially when you can use table value constructors. The feature seems to be hidden in documentation, as a matter of fact I found it only in the online documentation, the local help having no reference to it. Anyway, excepting a few posts on the same subject I can’t say I have seen any code until now making use of it. What’s interesting to note, before going into details, is that the ANSI SQL 94 (the earliest version of documentation I have) mentions vaguely table value constructors (p. 221), therefore I expect they are part of ANSI standard.

   According to MSDN a table value constructor allows a set of row value expressions to be constructed into a table within a single DML statement. As can be seen from the MSDN examples the list of values can contain a combination of list of values and queries, as long the data type matches and only a single scalar value is provided as a row value expression Another limitation concerns the maximum number of rows constructed, the maximum value being 1000. It’s important to know these limitations, because they might force you use other approaches. Before sketching a solution for this post’s problem, let’s look how a table can be constructed on the fly given a list of pair-values:

-- example table value constructor 
SELECT EmployeeId 
, VacationHours 
FROM ( VALUES (101, '23') 
     , (102, '82') 
     , (103, '66') 
     , (104, '17') 
      , (105, '64') 
      , (106, '56') 
      , (107, '107') 
      , (108, '50') 
      , (109, '109') 
      , (110, '48')) AS Data(EmployeeId, VacationHours) 

    The table thus constructed can be further used in an UPDATE statement, the following example being based on HumanResources.Employee from AdventureWorks database.

-- example table value constructor update UPDATE HumanResources.Employee 
SET VacationHours = Data.VacationHours 
FROM ( VALUES (101, '23') 
      , (102, '82') 
      , (103, '66') 
      , (104, '17') 
      , (105, '64') 
      , (106, '56') 
      , (107, '107') 
      , (108, '50') 
      , (109, '109') 
      , (110, '48')) AS Data(EmployeeId, VacationHours) 
WHERE HumanResources.Employee.EmployeeId = Data.EmployeeId 

    A similar construct can be be used to perform a SELECT, UPDATE or DELETE in which multiple columns participate in the JOIN. For example let’s consider a SELECT based on a set of pairs of values from the Sales.SalesOrderHeader table:

-- example table value constructor
SELECT SOH.* 
FROM Sales.SalesOrderHeader SOH 
     JOIN ( -- set of pairs 
        VALUES(10, 5069) 
         , (10, 6070) 
         , (10, 7060) 
         , (11, 407) 
         , (11, 1603) 
         , (11, 2737)) AS Data(CustomerId, CurrencyRateID) 
      ON SOH.CustomerId = Data.CustomerID 
    AND SOH.CurrencyRateID = Data.CurrencyRateID 

    As can be seen table value constructors are pretty simple to use, especially when dealing with a small number of values. If they are the best choice, that depends on the problem you are trying to solve and its specific constraints!