Introduction

Sometimes making a query in a stored procedure more versatile can deteriorate its performance. Dynamic SQL can be used to speed up the query by simplifying the SQL to the bare minimum, but there are some risks to be avoided. Dynamic SQL is more complicated to write than normal SQL and also harder to debug as the actual SQL statement that is executed is not easily visible. There is also a chance that the stored procedure is exposing itself to SQL Injection if not properly coded using parameters. This article shows an example of a poor performing query and how to make it perform better using Dynamic SQL. It also demonstrates a way of coding dynamic SQL so it can easily be debugged. When we write dynamic SQL within a stored procedure not only do we have to give the user EXECUTE rights to the procedure but also specific access to the tables in the dynamic SQL.

Poorly Performing Procedure

A project I was assigned to had a stored procedure that was taking a long time to execute. The purpose of the stored procedure is to return rows that match a given set of optional inputs. The way this is achieved using non-dynamic SQL is for the query to test the value of the parameter for NULL or a column equal to the value of the parameter. All columns on the table are defined to be NOT NULL and the passing in of a parameter with a NULL value logically means that we don't care what the value is. You might think that SQL Server would short-circuit the query when it sees that the parameter is null and not bother to look at the values on the table but it actually does a full table scan instead.

Example:

USE [AdventureWorks]
GO
CREATE PROCEDURE SearchProducts
(
    @Name nvarchar(50)        = NULL,
    @Color nvarchar(15)       = NULL,
    @ListPriceMin money       = NULL,
    @ListPriceMax money       = NULL,
    @ModifiedDateMin datetime = NULL,
    @ModifiedDateMax datetime = NULL
)
AS
BEGIN
    SELECT [Name],
           [Color],
           [ListPrice],
           [ModifiedDate]
      FROM [Production].[Product]
     WHERE ([Name] LIKE @Name                OR @Name IS NULL)
       AND ([Colour] LIKE @Color             OR @Color IS NULL)
       AND ([ListPrice] >= @ListPriceMin     OR @ListPriceMin IS NULL)
       AND ([ListPrice] <= @ListPriceMax     OR @ListPriceMax IS NULL)
       AND (ModifiedDate >= @ModifiedDateMin OR @ModifiedDateMin IS NULL)
       AND (ModifiedDate <= @ModifiedDateMax OR @ModifiedDateMax IS NULL)
END
GO

You will notice that for every parameter there is test in the SQL to see if it was passed in with a NULL value and if it was to mark the AND predicate a true. When SQL Server evaluates this query it does not work out in advance that the parameter passed in is NULL and so ignore trying to match the value to the column, instead it still tries to match the value to the column, the query ends up doing a full table scan even if there is an index on the column. If run on a table with many millions of rows will run very slowly. A way to speed this up is to use Dynamic SQL, that is generate the SQL statement within the stored procedure and then execute it.

A note on SQL Injection

SQL Injection can be quite amusing. For example there is a really funny cartoon strip about a mother who has named her son '); DROP TABLE Students;-- or 'Bobby Tables' for short. See http://xkcd.com/327/(it's well worth a quick look). However, SQL Injection is a serious matter and can lead to attacks that cause damage to data or access to information that should be secure.

Well Performing but open to SQL Injection Dynamic SQL

It can be tempting to the developer to simply construct the SQL with the actual parameter values that are passed into the procedure. However this might leave the procedure vulnerable to injected SQL.

Example:

USE [AdventureWorks]
GO
-- **********************************************************************
-- Sample:
-- EXEC [SearchProducts_SQLInjectable] 'W%', 'SH%'
-- Injected SQL
-- EXEC [SearchProducts_SQLInjectable] @Name='W%''; SELECT * FROM HumanResources.EmployeeAddress --'
-- **********************************************************************
CREATE PROCEDURE [dbo].[SearchProducts_SQLInjectable]
(
    @Name nvarchar(62)          = NULL,
    @ProductNumber nvarchar(25) = NULL,
    @ListPriceMin money         = NULL,
    @ListPriceMax money         = NULL,
    @ModifiedDateMin datetime   = NULL,
    @ModifiedDateMax datetime   = NULL
)
AS
BEGIN
    DECLARE @sSql nvarchar(4000)
    DECLARE @sWhereClause nvarchar(4000)
    DECLARE @NewLine nvarchar(4000)
    SET @NewLine = CHAR(13) + CHAR(10)
    SET @sWhereClause = '' -- Initialise
    SET @sSql = 'SELECT [Name],' + @NewLine
              + '       [ProductNumber],' + @NewLine
              + '       [ListPrice],' + @NewLine
              + '       [ModifiedDate]' + @NewLine
              + '  FROM [Production].[Product]' + @NewLine
              + ' WHERE 1 = 1' -- Always true
    IF @ProductNumber IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ProductNumber] LIKE ''' + @ProductNumber + ''''
    IF @ListPriceMin IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ListPrice] >= ' + Cast(@ListPriceMin as varchar(30))
    IF @ListPriceMax IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ListPrice] <= ' + Cast(@ListPriceMax as varchar(30))
    IF @ModifiedDateMin IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ModifiedDate >= ' + Convert(varchar(30), @ModifiedDateMin, 121)
    IF @ModifiedDateMax IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ModifiedDate <= ' + Convert(varchar(30), @ModifiedDateMax, 121)
    IF @Name IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [Name] LIKE ''' + @Name + '''' 
    SET @sSql = @sSql + @sWhereClause
    EXEC (@sSql)
END
GO

Constructing SQL this way is not good, it leaves the procedure open to SQL Injection; little Bobby Tables might enter his name! Also, it does not allow the optimiser to reuse the execution plan. An example of how SQL Injection can be performed is given in the comments below:

EXEC [SearchProducts_SQLInjectable] @Name='W%''; SELECT * FROM HumanResources.EmployeeAddress --'

A slightly better way to construct the WHERE clause and provide a little more protection is to double up the quotes in each parameter by calling SQL Server's Replace function e.g.:

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE ''' + Replace(@Name, '''', '''''') + '''' 

This is still not optimal as it still does not allow SQL Server to reuse the query when different values of @Name are passed in to the procedure, though it should prevent most forms of SQL injection.

Using Parameterised Dynamic SQL

Using this method we execute the dynamic SQL with parameters for the variables and it is impossible for SQL injection to take place.

Example:

USE [AdventureWorks]
GO
-- **********************************************************************
-- Sample CALL:
-- EXEC [SearchProducts_SQLNonInjectable] 'W%'
-- **********************************************************************
CREATE PROCEDURE [dbo].[SearchProducts_SQLNonInjectable]
(
    @Name nvarchar(50) = NULL,
    @ProductNumber nvarchar(25) = NULL,
    @ListPriceMin money = NULL,
    @ListPriceMax money = NULL,
    @ModifiedDateMin datetime = NULL,
    @ModifiedDateMax datetime = NULL
)
AS
BEGIN
    DECLARE @sSql nvarchar(4000)
    DECLARE @sWhereClause nvarchar(4000)
    DECLARE @ParmDefinition nvarchar(4000)
    DECLARE @NewLine nvarchar(4000)
    SET @NewLine = CHAR(13) + CHAR(10)
    SET @sWhereClause = '' -- Initialise
    SET @sSql = 'SELECT [Name],' + @NewLine
              + '      [ProductNumber],' + @NewLine
              + '      [ListPrice],' + @NewLine
              + '      [ModifiedDate]' + @NewLine
              + ' FROM [Production].[Product]' + @NewLine
              + 'WHERE 1 = 1' -- Always true 
    IF @Name IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [Name] LIKE @Name'
    IF @ProductNumber IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ProductNumber] LIKE @ProductNumber'
    IF @ListPriceMin IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ListPrice] >= @ListPriceMin'
    IF @ListPriceMax IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ListPrice] <= @ListPriceMax'
    IF @ModifiedDateMin IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ModifiedDate >= @ModifiedDateMin'
    IF @ModifiedDateMax IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ModifiedDate <= @ModifiedDateMax '
    SET @ParmDefinition = '@Name            nvarchar(50),' + @NewLine
                        + '       @ProductNumber   nvarchar(25),' + @NewLine
                        + '       @ListPriceMin    money,' + @NewLine
                        + '       @ListPriceMax    money,' + @NewLine
                        + '       @ModifiedDateMin datetime,' + @NewLine
                        + '       @ModifiedDateMax datetime'
    SET @sSql = @sSql + @sWhereClause
    EXEC sp_executesql @sSql, @ParmDefinition,
                       @Name=@Name,
                       @ProductNumber=@ProductNumber,
                       @ListPriceMin=@ListPriceMin,
                       @ListPriceMax=@ListPriceMax,
                       @ModifiedDateMin=@ModifiedDateMin,
                       @ModifiedDateMax=@ModifiedDateMax
END
GO

Debugging Parameterised Dynamic SQL

Using Dynamic SQL can make debugging the code quite difficult. I write procedures that use Dynamic SQL in a way that allows easy debugging by adding an additional parameter to the procedure and using some PRINT statements. The additional parameter is called @Debug, is of type bit, and when set to a value of 1 will print out the SQL that would have been executed with all the values of the parameters. The example given is quite a simple one so it is not really necessary in this case, but Dynamic SQL can be as complex as you like and being able to debug it can significantly speed up development time. The example below shows the same stored procedure with the debug parameter. In addition to this it includes a select statement for the table, which is never executed, this is so that if the system procedure sp_depends is used to find out which procedures use which tables then this stored procedure will be listed. To execute sp_depends the following command can be entered into management studio:

EXEC sp_depends 'Production.Product'

Example:

USE [AdventureWorks]
GO
-- **********************************************************************
-- Sample:
-- EXEC SearchProducts_SQLNonInjectableWithDebug 'W%', 'S%'
-- **********************************************************************
CREATE PROCEDURE [dbo].[SearchProducts_SQLNonInjectableWithDebug]
(
    @Name nvarchar(50) = NULL,
    @ProductNumber nvarchar(25) = NULL,
    @ListPriceMin money = NULL,
    @ListPriceMax money = NULL,
    @ModifiedDateMin datetime = NULL,
    @ModifiedDateMax datetime = NULL,
    @Debug bit = 0 -- When set to 1 just prints SQL and does not execute it
)
AS
BEGIN
    DECLARE @sSql nvarchar(4000)
    DECLARE @sWhereClause nvarchar(4000)
    DECLARE @ParmDefinition nvarchar(4000)
    DECLARE @NewLine nvarchar(4000)
    SET @NewLine = CHAR(13) + CHAR(10)
    SET @sWhereClause = '' -- Initialise
    SET @sSql = 'SELECT [Name],' + @NewLine
              + '       [ProductNumber],' + @NewLine
              + '       [ListPrice],' + @NewLine
              + '       [ModifiedDate]' + @NewLine
              + '  FROM [Production].[Product]' + @NewLine
              + ' WHERE 1 = 1' -- Always true 
    IF @Name IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [Name] LIKE @Name'
    IF @ProductNumber IS NOT NULL
SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ProductNumber] LIKE @ProductNumber'
    IF @ListPriceMin IS NOT NULL
SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ListPrice] >= @ListPriceMin'
    IF @ListPriceMax IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ListPrice] <= @ListPriceMax'
    IF @ModifiedDateMin IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ModifiedDate >= @ModifiedDateMin'
    IF @ModifiedDateMax IS NOT NULL
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ModifiedDate <= @ModifiedDateMax'
    SET @ParmDefinition = '@Name nvarchar(50),' + @NewLine
                        + '       @ProductNumber nvarchar(25),' + @NewLine
                        + '       @ListPriceMin money,' + @NewLine
                        + '       @ListPriceMax money,' + @NewLine
                        + '       @ModifiedDateMin datetime,' + @NewLine
                        + '       @ModifiedDateMax datetime'
    SET @sSql = @sSql + @sWhereClause
    IF @Debug = 0
        EXEC sp_executesql @sSql,@ParmDefinition,
                           @Name=@Name,
                           @ProductNumber=@ProductNumber,
                           @ListPriceMin=@ListPriceMin,
                           @ListPriceMax=@ListPriceMax,
                           @ModifiedDateMin=@ModifiedDateMin,
                           @ModifiedDateMax=@ModifiedDateMax
    ELSE
        BEGIN
            PRINT 'DECLARE ' + @ParmDefinition
            PRINT ''
            PRINT 'SET @Name = ' + IsNull('''' + @Name + '''','NULL')
PRINT 'SET @ProductNumber = ' + IsNull('''' + @ProductNumber + '''','NULL')
            PRINT 'SET @ListPriceMin = ' + IsNull(Cast(@ListPriceMin AS varchar(30)),'NULL')
            PRINT 'SET @ListPriceMax = ' + IsNull(Cast(@ListPriceMax AS varchar(30)),'NULL')
PRINT 'SET @ModifiedDateMin = ' + IsNull('''' + Convert(varchar(50), @ModifiedDateMin, 121) + '''','NULL')
            PRINT 'SET @ModifiedDateMax = ' + IsNull('''' + Convert(varchar(50), @ModifiedDateMax, 121) + '''','NULL')
            PRINT ''
            PRINT @sSql 
        END
    --END IF
    IF 1 = 2 -- Always false
        BEGIN -- Make sure this procedure is detected with sp_depends on table [Production].[Product]
            SELECT TOP(0) 0 FROM [Production].[Product]
        END
    --END IF
END
GO

Conclusion

This article has demonstrated how to use dynamic SQL with parameters and some debug tips, it is not only necessary to give the user of the stored procedure EXECUTE permissions to the procedure but they also need to have access to the table in the dynamic SQL. In the example above we would have to grant SELECT access to the user on the table Production.Product. Doing this could make the application vulnerable to SQL Injection from a different procedure that might accidentally have been written in a way that would allow SQL injection. A way to allow the users of the stored procedure to execute the procedure without granting them select access is to sign the stored procedure with a certificate. I will cover how to do this in my next article "Using Certificates to Sign Stored Procedures".