Dynamic Search Conditions in T-SQL

Introduction

Performance

You might have been told: Don't use dynamic SQL, it's slow!. Yes, used wrongly and in the wrong place dynamic SQL can degrade performance, and sometimes severely. But if you use sp_executesql and you always refer to your tables in two-part notation, the penalty for dynamic SQL is about negligible. On the other hand, if you use EXEC() and refer to tables with only the table name there can be a cost – not for running the query itself. The cost is for building the query plan. With stored procedures and correctly written dynamic SQL, SQL Server can find a query plan in cache that it can reuse.

For a dynamic search like our search_orders, there is no single plan that fits all combinations of input parameters. One plan which may give sub-second response when the user specifies the product, may take two minutes if the user specifies the customer or vice versa. In this case, some extra 100 ms to build a query plan for each new combination of search parameters is a price well worth paying. Particularly, if the plan stays in cache and can be reused by other users.

Thus, from a performance perspective, dynamic SQL is often a superior solution for this sort of problem.

Security

On SQL 2000, there is an important security aspect you must be aware of. Recall that with stored procedures, users do not need to have direct access rights to the tables and views the procedures refer to; they only need permission to execute the procedures. When a stored procedure is running, the rights of the procedure owner apply. However, dynamic SQL created in a stored procedure is not part of that procedure, and when the dynamic SQL code executes, it is the permissions of the current user that count. Thus, to use dynamic SQL you need to grant your users SELECT access directly to the tables. If this is not acceptable in your system, you cannot use dynamic SQL.

...well, it is not 100% true. There are hybrid solutions where you put the static SQL in a view or an inline-table function. Particularly the latter can give you almost all the power of dynamic SQL, without having to expose tables directly to the users. We will look into this in the section Hybrid Solutions – Using Both Static and Dynamic SQL.

On SQL 2005, there are two more ways to avoid the permissions issue: You can sign the procedure with a certificate and then associate the certificate with a virtual user that you grant the necessary permissions. You can also use the EXECUTE AS clause to impersonate such a virtual user. The latter solution is less hassle to implement, but it has side effects that can break row-level security schemes and make system monitoring more difficult. I describe both these methods in detail in my article Granting Permissions through Stored Procedures.

Further Reading

There are more things to say about dynamic SQL as such, that I will not go into here. Rather I refer you to my web article The Curse and Blessings of Dynamic SQL, which discusses the use of dynamic SQL in general. In this article, I also cover the performance and security topics in more detail.

Testing is Necessary!

Because of the way you generate the code, it is very important that you test all input parameters, and preferably some combinations of them too. If you are not careful, the users can experience syntax errors from your code when you build queries dynamically.

You should also test that you get the desired performance, using data volumes in parity to what you can expect in production.

The Methods to Do Dynamic SQL

There are three ways to go:

1. A T-SQL procedure that builds a query string and executes it with sp_executesql.
2. A CLR procedure that builds a parameterised query string and executes it. (SQL 2005 only.)
3. A T-SQL procedure that builds a query string and executes it with EXEC().

Of these three, the first two are both good choices, and use what you find the most convenient. Using EXEC() is a inferior method that is more difficult to work with and your prospects for query-plan reuse are slim. There are however a few special situations where EXEC() and the techniques around it comes into play.

I will in detail discuss an implementation of search_orders that uses sp_executesql, and I will also present two CLR implementations. I also show an implementation of search_orders that uses EXEC(), but this can be considered extra reading that you can skip if you like. Before I move on to static SQL, I will look a little at situations where cached query plans hampers your performance. This, too, you can regard as extra reading and skim over at first reading.

Using sp_executesql

sp_executesql is a system procedure that takes an SQL statement as its first parameter, and a declaration of parameters as the second parameter, and the remaining parameters are determined by that parameter list. Here is the procedure search_orders_1, which uses sp_executesql:

CREATE PROCEDURE search_orders_1                                   --  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 @sql        nvarchar(4000),                                -- 16
        @paramlist  nvarchar(4000)                                 -- 17
                                                                   -- 18
SELECT @sql =                                                      -- 19
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     -- 20
            c.CustomerID, c.CompanyName, c.Address, c.City,        -- 21
            c.Region,  c.PostalCode, c.Country, c.Phone,           -- 22
            p.ProductID, p.ProductName, p.UnitsInStock,            -- 23
            p.UnitsOnOrder                                         -- 24
     FROM   dbo.Orders o                                           -- 25
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       -- 26
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID         -- 27
     JOIN   dbo.Products p ON p.ProductID = od.ProductID           -- 28
     WHERE  1 = 1'                                                 -- 29
                                                                   -- 30
IF @orderid IS NOT NULL                                            -- 31
   SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +             -- 32
                        ' AND od.OrderID = @xorderid'              -- 33
                                                                   -- 34
IF @fromdate IS NOT NULL                                           -- 35
   SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'           -- 36
                                                                   -- 37
IF @todate IS NOT NULL                                             -- 38
   SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate'             -- 39
                                                                   -- 40
IF @minprice IS NOT NULL                                           -- 41
   SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice'          -- 42
                                                                   -- 43
IF @maxprice IS NOT NULL                                           -- 44
   SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice'          -- 45
                                                                   -- 46
IF @custid IS NOT NULL                                             -- 47
   SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' +           -- 48
                        ' AND c.CustomerID = @xcustid'             -- 49
                                                                   -- 50
IF @custname IS NOT NULL                                           -- 51
   SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%''' -- 52
                                                                   -- 53
IF @city IS NOT NULL                                               -- 54
   SELECT @sql = @sql + ' AND c.City = @xcity'                     -- 55
                                                                   -- 56
IF @region IS NOT NULL                                             -- 57
   SELECT @sql = @sql + ' AND c.Region = @xregion'                 -- 58
                                                                   -- 59
IF @country IS NOT NULL                                            -- 60
   SELECT @sql = @sql + ' AND c.Country = @xcountry'               -- 61
                                                                   -- 62
IF @prodid IS NOT NULL                                             -- 63
   SELECT @sql = @sql + ' AND od.ProductID = @xprodid' +           -- 64
                        ' AND p.ProductID = @xprodid'              -- 65
                                                                   -- 66
IF @prodname IS NOT NULL                                           -- 67
   SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' -- 68
                                                                   -- 69
SELECT @sql = @sql + ' ORDER BY o.OrderID'                         -- 70
                                                                   -- 71
IF @debug = 1                                                      -- 72
   PRINT @sql                                                      -- 73
                                                                   -- 74
SELECT @paramlist = '@xorderid   int,                              -- 75
                     @xfromdate  datetime,                         -- 76
                     @xtodate    datetime,                         -- 77
                     @xminprice  money,                            -- 78
                     @xmaxprice  money,                            -- 79
                     @xcustid    nchar(5),                         -- 80
                     @xcustname  nvarchar(40),                     -- 81
                     @xcity      nvarchar(15),                     -- 82
                     @xregion    nvarchar(15),                     -- 83
                     @xcountry   nvarchar(15),                     -- 84
                     @xprodid    int,                              -- 85
                     @xprodname  nvarchar(40)'                     -- 86
                                                                   -- 87
EXEC sp_executesql @sql, @paramlist,                               -- 88
                   @orderid, @fromdate, @todate, @minprice,        -- 89
                   @maxprice,  @custid, @custname, @city, @region, -- 90
                   @country, @prodid, @prodname                    -- 91

In case you are reading this with a narrow browser window, I should point out that there are line numbers to the right that I will refer to in the following text.

Overall Flow

On lines 19-29, I compose the basic SQL string. The condition WHERE 1 = 1 on line 29 is there to permit the users to call the procedure without specifying any parameters at all.

Then on lines 31-68, I check all parameters (save @debug), and if a parameter is non-NULL, I add a condition for the corresponding column to the SQL string. Finally on line 70, I add the ORDER BY clause.

On line 72, I test the @debug parameter. If 1, I print the SQL string. This is handy, if the dynamic SQL yields a compilation error that I don't understand. Once I see the SQL code the error might be apparent. A typical error is to miss a space, leading to code that reads:

WHERE  1 = 1 AND o.OrderDate <= @xtodateAND p.ProductName LIKE @xprodname

On lines 75-86 I declare the parameter list for my dynamic SQL statement, and on lines 88-91 I finally execute it.

A Little More in Detail on sp_executesql

sp_executesql is a system procedure with a very special parameter list. The first parameter is a parameterised SQL statement. The second parameter is a parameter-list declaration, very similar to the parameter list to a stored procedure. And the remaining parameters are simply the parameters defined by that parameter-list parameter.

 

The parameterised query is placed in cache, so if a second user make a search using the same input parameters (for instance @city and @prodname), the query plan will be reused, even if he uses different values in his search.

The parameter-list parameter that I pass to sp_executesql is basically the same as the parameter list to the procedure itself. Here, I have called the parameters @xorderid and so on. There is not any technical reason for this, and normally I would have re-used the names in the parameter list of the stored procedure. But I wanted to make you aware of that the parameters inside the dynamic SQL have no relation to the parameters and variables in the surrounding stored procedure. The dynamic SQL constitutes a scope on its own. Think of it as a stored procedure that you create on the fly.

The SQL statement and the parameter must be Unicode strings, so the @sql and @paramlist variables must be nvarchar. And if you pass the SQL statement or the parameter-list as literals, you must use N (for National) denote a Unicode literal:

EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID = @orderid',
                   N'@orderid int', @orderid = 10872

You can use OUTPUT parameters too with sp_executesql. I'm not showing any example of this here, but see the section on sp_executesql in Curse and Blessings...

Declaring the SQL String

You can see that on line 16, I have this declaration:

DECLARE @sql        nvarchar(4000)

In SQL 7 and SQL 2000, there is no way to work with variables with more than 8000 bytes, which imposes a practical limit when working with sp_executesql, although the statement parameter to sp_executesql is ntext on these versions of SQL Server. It's still possible to work with longer query strings if you use EXEC(). (You can even wrap sp_executesql in EXEC(). There is an example in Curse and Blessings... of this.)

SQL 2005 introduced a new data type, nvarchar(MAX) which is unlimited just like ntext, but without all the restrictions. You can declare your @sql variable to be nvarchar(MAX) and leave worries behind about outgrowing the variable. (I did not use nvarchar(MAX) in search_orders_1 so that it would run on SQL 7 and SQL 2000 as well.)

The dbo Prefix

On lines 25-28 there is something very important:

     FROM   dbo.Orders o
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
     JOIN   dbo.Products p ON p.ProductID = od.ProductID

As you can see, I refer to all tables in two-part notation. That is, I also specify the schema (which in SQL 7/2000 parlance normally is referred to as owner.) If I would leave out the schema, each user would get his own his own private version of the query plan, so if there are ten users searching for orders there will be ten instances of the plan in cache. (As discussed in Curse and Blessings... it may work without specifying the schema on SQL 2005, but you should not rely on that.)

(The author must shamefully confess that earlier versions of this article did not prefix the tables with dbo, and yet I said that query plans would be reused by other users.)

Double Feature

You may wonder why there are two conditions on the order id, customer id and product id (lines 32-33, 48-49 and 64-65). This is because I once learnt that you should always specify all conditions you know of to help the optimizer out. That was very long ago, and the rule may no longer apply. But I included it here nevertheless.

Picking Different Tables

Assume for the example, that there had been a table Historicorders in Northwind for orders that are shipped, invoiced and paid for, and users should be given the choice to search either current or historic orders. (For simplicity, I'm overlooking Order Details here.) How would you solve this?

You may be tempted to try something like SELECT ... FROM @ordertable and then add @ordertable as a parameter. However, T-SQL does not permit you to parameterise the table name. (And for good reasons as I discuss in Curse and Blessings...)

In fact, you should not pass the table name as a parameter to the procedure at all; once you have started to use stored procedures, all references to table and column names should be in the procedures themselves. The correct way would be add another parameter to search_orders:

@historic_data  bit DEFAULT = 0

and then line 25 would read:

FROM dbo.' + CASE @historic_data
                  WHEN 0 THEN 'Orders'
                  WHEN 1 THEN 'Historicorders'
              END + ' o

Trying it out

Here are some test cases:

EXEC search_orders_1 @orderid = 11000
EXEC search_orders_1 @custid  = 'ALFKI'
EXEC search_orders_1 @prodid  = 76
EXEC search_orders_1 @prodid  = 76, @custid = 'RATTC'
EXEC search_orders_1 @fromdate = '19980205', @todate = '19980209'
EXEC search_orders_1 @city = 'Bräcke', @prodid = 76

If you try these and inspect the query plans, you will see that in the available indexes on the search columns is used in Northgale with one exception: the index on Customers.City is not used for the last case, but this column is not very selective. If you run the queries in Northwind, you will see more scans, but this is due to the small size of that database.