About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Friday, June 17, 2011

Pulling the Strings of SQL Server–Part 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
SELECT *
FROM
Person.Address

WHERE
AddressID = 1

    The query could be encapsulated in a string:
-- example 2
EXEC ('SELECT * FROM Person.Address WHERE AddressID = 1')
    Or it could be stored in a string variable:
-- example 2
EXEC ('SELECT * FROM Person.Address WHERE AddressID = 1')
    Supposing that the AddressID is considered as parameter we can write:
-- example 4
DECLARE @AddressID int
SET
@AddressID = 1

SELECT
*

FROM Person.Address
WHERE AddressID = @AddressID
    And here’s its dynamic form:
-- example 5
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.

No comments: