Dynamic Search Conditions in T-SQL

As I've already mentioned, EXEC() is an inferior solution to sp_executesql and the CLR. The reason for this is that since EXEC() does not take parameters, you have to build a query string with parameter values interpolated. This precludes query plans from being reused. It also makes the programming more difficult.

Nevertheless, there are three situations where you need to use EXEC() or at least interpolate parameter values:

• You are on SQL 7 or SQL 2000, and the query string could exceed 4000 characters. As you can say

  EXEC(@sql1 + @sql2 + @sql3 ...)

  there is no practical limit to the length of the query string with EXEC(). On SQL 2005 you can use nvarchar(MAX), with sp_executesql, so this issue does not exist there.

• You use a hybrid solution with a inline table function, a solution I will discuss in detail later in this article.
• You opt to interpolate the value of a certain parameter into the query. We will look into why you want to this in the section When Caching Is Not Really What You Want.

So while it's not equally essential to master EXEC() as sp_executesql to build dynamic search functions, it can still be worthwhile to study the technique so you can avoid the pitfalls if you would need to use it. But feel free to skip this section in your first reading if you are in a hurry and come back when you actually need to use it.

search_orders_2

In difference to sp_executesql, EXEC() only takes a string as a parameter, so you can not parameterise the query. Instead you have to put the values into the SQL string. At first glance you may find this simpler, but as we shall see, this is in fact more complicated. Here is the procedure search_orders_2:

CREATE PROCEDURE search_orders_2                                   --  1
                 @orderid   int          = NULL,                   --  2
                 @fromdate  datetime     = NULL,                   --  3
                 @todate    datetime     = NULL,                   --  4
                 @minprice  money        = NULL,                   --  5
                 @maxprice  money        = NULL,                   --  6
                 @custid    nchar(5)     = NULL,                   --  7
                 @custname  nvarchar(40) = NULL,                   --  8
                 @city      nvarchar(15) = NULL,                   --  9
                 @region    nvarchar(15) = NULL,                   -- 10
                 @country   nvarchar(15) = NULL,                   -- 11
                 @prodid    int          = NULL,                   -- 12
                 @prodname  nvarchar(40) = NULL,                   -- 13
                 @debug     bit          = 0 AS                    -- 14
                                                                   -- 15
DECLARE @sql1        nvarchar(4000),                               -- 16
        @sql2        nvarchar(4000),                               -- 17
        @fromdatestr char(23),                                     -- 18
        @todatestr   char(23),                                     -- 19
        @minpricestr varchar(25),                                  -- 20
        @maxpricestr varchar(25)                                   -- 21
                                                                   -- 22
SELECT @fromdatestr = convert(char(23), @fromdate, 126),           -- 23
       @todatestr   = convert(char(23), @todate, 126),             -- 24
       @minpricestr = convert(varchar(25), @minprice),             -- 25
       @maxpricestr = convert(varchar(25), @maxprice)              -- 26
                                                                   -- 27
SELECT @sql1 =                                                     -- 28
   'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,      -- 29
           c.CustomerID, c.CompanyName, c.Address, c.City,         -- 30
           c.Region, c.PostalCode, c.Country, c.Phone,             -- 31
           p.ProductID, p.ProductName, p.UnitsInStock,             -- 32
           p.UnitsOnOrder                                          -- 33
    FROM   dbo.Orders o                                            -- 34
    JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID        -- 35
    JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID          -- 36
    JOIN   dbo.Products p ON p.ProductID = od.ProductID            -- 37
    WHERE  1 = 1'                                                  -- 38
                                                                   -- 39
IF @orderid IS NOT NULL                                            -- 40
   SELECT @sql1 = @sql1 + ' AND o.OrderID = ' + str(@orderid) +    -- 41
                          ' AND od.OrderID = ' + str(@orderid)     -- 42
                                                                   -- 43
IF @fromdate IS NOT NULL                                           -- 44
   SELECT @sql1 = @sql1 + ' AND o.OrderDate >= ' +                 -- 45
                          quotename(@fromdatestr, '''')            -- 46
                                                                   -- 47
IF @todate IS NOT NULL                                             -- 48
   SELECT @sql1 = @sql1 + ' AND o.OrderDate <= ' +                 -- 49
                          quotename(@todatestr, '''')              -- 50
                                                                   -- 51
IF @minprice IS NOT NULL                                           -- 52
   SELECT @sql1 = @sql1 + ' AND od.UnitPrice >= ' + @minpricestr   -- 53
                                                                   -- 54
IF @maxprice IS NOT NULL                                           -- 55
   SELECT @sql1 = @sql1 + ' AND od.UnitPrice <= ' + @maxpricestr   -- 56
                                                                   -- 57
SELECT @sql2 = ''                                                  -- 58
                                                                   -- 59
IF @custid IS NOT NULL                                             -- 60
   SELECT @sql2 = @sql2 + ' AND o.CustomerID = ' +                 -- 61
                          quotename(@custid, '''') +               -- 62
                          ' AND c.CustomerID = ' +                 -- 63
                          quotename(@custid, '''')                 -- 64
                                                                   -- 65
IF @custname IS NOT NULL                                           -- 66
   SELECT @sql2 = @sql2 + ' AND c.CompanyName LIKE ' +             -- 67
                          quotename(@custname + '%', '''')         -- 68
                                                                   -- 69
IF @city IS NOT NULL                                               -- 70
   SELECT @sql2 = @sql2 + ' AND c.City = ' +                       -- 71
                          quotename(@city, '''')                   -- 72
                                                                   -- 73
IF @region IS NOT NULL                                             -- 74
   SELECT @sql2 = @sql2 + ' AND c.Region = ' +                     -- 75
                          quotename(@region, '''')                 -- 76
                                                                   -- 77
IF @country IS NOT NULL                                            -- 78
   SELECT @sql2 = @sql2 + ' AND c.Country = ' +                    -- 79
                          quotename(@country, '''')                -- 80
                                                                   -- 81
IF @prodid IS NOT NULL                                             -- 82
   SELECT @sql2 = @sql2 + ' AND od.ProductID = ' + str(@prodid) +  -- 83
                          ' AND p.ProductID = ' + str(@prodid)     -- 84
                                                                   -- 85
IF @prodname IS NOT NULL                                           -- 86
   SELECT @sql2 = @sql2 + ' AND p.ProductName LIKE ' +             -- 87
                          quotename(@prodname + '%', '''')         -- 88
                                                                   -- 89
IF @debug = 1                                                      -- 90
   PRINT @sql1 + @sql2                                             -- 91
                                                                   -- 92
EXEC(@sql1 + @sql2 + ' ORDER BY o.OrderID')                        -- 93

General Notes

When building an non-parameterised query with the values included, you need to be disciplined when you write your code. It's very easy to get lost in a maze of nested quotes. I often see people on the newsgroups posting code like:

EXEC('SELECT col1, col2, ...
      FROM   ...
      WHERE  ' + CASE @par1 IS NULL THEN ' + col = ''' + @par  + ''' + ...)

This is difficult to read and maintain, and if it goes wrong, you have no idea what SQL you are actually generating. search_orders2 aggregates the SQL code in two variables, and there is a @debug parameter so I can see the SQL code, if I need to verify that I am generating the right thing. And by using the function quotename(), I have tried to reduce the need for nested quotes to a minimum. (More about this function just below.)

Keep in mind that EXEC() opens a new scope. The SQL you generate can not refer to variables declared in the surrounding procedure, and if the dynamic SQL creates a temp table, that table goes away with the scope.

The Use of Quotename()

As you can see, starting from line 46, I make frequent use of the built-in function quotename(), an idea I got from SQL Server MVP Steve Kass. quotename() takes two parameters: a string and a delimiter character. If the delimiter is (, [, < or {, the corresponding bracket is assumed to be the closing delimiter. The return value is the string enclosed by the delimiters, and any closing delimiter in the string is doubled. The default delimiter is square brackets. Examples:

SELECT quotename('Order Details')                     -- => [Order Details]
SELECT quotename('Two o' + char(39) + 'clock', '''')  -- => 'Two o''clock'
SELECT quotename('abc{}def', '{')                     -- => {abc{}}def}

Of these examples, the interesting one in this context is the second. In T-SQL you use single quotes to delimit strings. If you need to include the string delimiter in a literal, you double it, so those four single quotes in a row is a one-character literal with the value of a single quote ('). Alternatively, you can express this value as char(39) like I do when I form the string Two o'clock in the example above.

Now, why all this? There are three important reasons:

1. It makes the code much easier to write. If you don't use quotename(), you need to use nested quotes, and the code becomes really messy.
2. This permits the user to enter values such as Let's Stop N Shop for the company name (this is an actual customer in the Northwind database) without getting a syntax error.
3. This increases you protection you against something which is known as SQL injection, a technique whereby a malicious intruder enters the string delimiter and then types in an SQL command where you as a naïve developer only expected data, and thereby manages to get SQL Server to execute that statement. See Curse and Blessings... for a longer discussion on SQL injection.

Note: The input parameter to quotename() is nvarchar(128), so if you have longer input parameters, you cannot use quotename(). You still need to be able to handle single quotes in the string. You can easily do this with the replace() function or use the quotestring() function from Curse and Blessings...

Datetime Parameters

All non-string parameters must be converted to string literals and on lines 23-24 I take care of the datetime parameters. The expression I use:

   convert(char(23), @fromdate, 126)

is not something I picked at whim. The resulting format is like this: 2003-04-06T21:14:26.627, which is the precise format that is mandated by the standard ISO 8601, and which is commonly used in XML. More importantly, it is one of the three formats for datetime literals in SQL Server of which the interpretation does not depend on the settings for date format and language. If you don't understand what I'm talking about, try these statements:

 

SET DATEFORMAT mdy
SELECT convert(datetime, '02/07/09')
SET DATEFORMAT dmy
SELECT convert(datetime, '02/07/09')
go
SELECT convert(datetime, '2002-12-19')   -- Fails!
go
SET LANGUAGE Swedish
SELECT convert(datetime, 'Oct 12 2003')  -- Fails! (It's "Okt" in Swedish.)

Beside the SET commands, the language setting (which also controls the date format) can be set on user-level with the system procedure sp_defaultlanguage.

The style parameter 126, as well the format with the T between date and time, is available only on SQL 2000 and later. On SQL 7 you should instead use 112 for the style parameter, which gives the format 20030406, a second date format that is independent of date-format and language settings. The drawback with 112 is you do not get hours and minutes. For the procedure search_orders_2 this would be OK, since users are only giving dates, but if you need to include the time portion on SQL 7, you need to combine style parameters:

SELECT convert(char(8), getdate(), 112) + ' ' +
       convert(char(8), getdate(), 108)

For further information about the various style parameters you can give to convert(), look under CAST and CONVERT in the T-SQL Reference of Books Online.

Numeric Parameters

On lines 25-26 I take care of the two money parameters, and on lines 41-42 and 83-84, I handle the int parameters @orderid and @prodid. As you see, I apply different strategies. Of course, I could have used convert() for @orderid and @prodid too. It is just that I find str() a little more convenient to use for integer values. str() works with money too, but with no further parameters, the value is rounded to the nearest whole number, whereas convert() by default retains two decimals, which is why I preferred convert() for the @min/maxprice parameters.

I'm not discussing the float and decimal data types here. Refer to the topics CAST and CONVERT and STR in Books Online.

@sql1 and @sql2

As you see, to build the string I use two variables, @sql1 and @sql2. In this example there is not really any reason for this, but if you have a huge query and large amount of parameters there is a risk on SQL 2000 and SQL 7 that your SQL string will exceed the maximum of 4000 characters for an nvarchar value (or even 8000 for varchar.) So to demonstrate the technique, there are two variables in search_orders_2. As you see, EXEC() permits you to pass an expression as a parameter. Note this is not an arbitrary expression; the only permitted operator is string concatenation.

On SQL 2005 you should use nvarchar(MAX), and you would have no reason to use more than one @sql variable.

varchar and nvarchar

The Northwind database consistently uses the nvarchar data type, but I've failed to handle this in my procedure. The data in Northwind appears to be restricted to the characters in Windows Latin-1, which covers languages such as English, Spanish, French and German, so if you have a system collation based on this character set, you would never get any incorrect response from search_orders_2. However, a Russian user trying:

EXEC search_orders_2 @city = N'Bräcke'

would not get the orders from Folk och Fä HB in Bräcke, because Bräcke would be converted to varchar as Bracke. This is how my procedure should have read: (lines 71-72)

SELECT @sql2 = @sql2 + ' AND c.City = N' +
                        quotename(@city, '''')

That is, I should have added an N to make the resulting SQL read c.City = N'Bräcke'. That N makes the literal a Unicode literal of the nvarchar data type, and it should appear with all nvarchar parameters. (The N stands for National, and is taken from the ANSI standard SQL-92.)

So why did I not give you the proper code? Well, I figured that many readers would take my code as a template for their own code, and I don't want you to include that N by routine. Because, if you use a nvarchar literal together with an indexed varchar column, the varchar column will be converted to nvarchar, and that precludes use of the index. (Because the index is sorted on an 8-bit value, not a 16-bit value.) Thus, you would get the right result, but performance would be poor.

Caching

As I've already mentioned, you get poor use of the cache with EXEC(), since the exact query string is placed in cache. This means that a search on customers ALFKI and VINET generates two different plans in the cache, so reuse only happens when two users performs exactly the same search.

Two qualifications here:

1. Sometimes SQL Server does not cache unparameterised queries at all, so there will be no reuse, even when two users search for the same thing.
2. SQL Server can auto-parameterise queries, but this happens only with the simplest queries, and is nothing you should rely on. On SQL 2005, there is something called forced parameterisation, where all queries are auto-parameterised, but again, you should not rely on this setting being active.

Summary

Just to make the point once more, here is a summary of the advantages of using sp_executesql over EXEC():

• Minimises the need to use nested quotes.
• Minimises risk for SQL injection.
• No need to worry about the formatting of datetime and numeric parameters.
• No need to think about whether use N before string literals or not. (But of course in the parameter list to sp_executesql you need to make the correct choice of data type.)
• Query plans for the same set of input parameters can be reused from cache.