Got more questions? Find advice on: ASP | XML | Regular Expressions | Windows
in Search
Welcome to SqlAdvice Sign in | Join | Help

The Penton-izer

I have moved to http://pentonizer.com so join me there!!!

Not-so Dynamic Sql

Suppose you need to exclude or include parameters to a sql statement.  Let's take this example:

CREATE PROCEDURE dbo.myProc
@pkA int
, @pkB varchar(10)
, @pkC decimal(12, 2)

AS

SELECT

    t.colA, t.colB, t.colC
FROM
    dbo.myTable t
WHERE
    t.pkA = @pkA
    AND t.pkB = @pkB
    AND t.pkC = @pkC

This procedure expects three parameters, as does the sql statement.  So we want to omit the parameters based on being set to NULL.  There are several ways to do this:

  1. Use dynamic sql in your client language
  2. Use dynamic sql in your stored procedure
  3. Use conditional logic in your query with various combinations of OR, AND or COALESCE/ISNULL in your query
  4. Use structured dynamic sql in a stored procedure

Let me give an example of each:

  1. Build the sql in a string such as this:

    string sql = “SELECT colA FROM tblA“;
    if(prmB != null && prmB.Length != 0) sql += “ WHERE colB = '“ + prmB.Replace(“'“, “''“) + “'“; 

    You have to contend with ensuring that your logic in the final query is correct.  This is easy with a single parameter, but when you end up having more than one in your WHERE clause (or other clauses) then the logic can get quite complex.  We also needed to deal with SQL Injection.  Of course, this is more easily dealt with by using parameters on command objects (like using a SqlParameter with a SqlCommand object). 
  2. This is similar to the above but you are doing the logic in the stored procedure.  You still need to contend with sql injection in this case.
  3. Conditional logic in your query means resorting to using OR and AND when needed:

    SELECT colA FROM tblA
    WHERE (colB = @colB OR @colB IS NULL)

    -- OR --

    SELECT colA FROM tblA
    WHERE colB = COALESCE(@colB, colB)

    This suffers from branching logic in your query.  It becomes more an more difficult for SQL Server to make a realistic execution plan for this type of query, especially when more conditions are present.
  4. Use structured dynamic sql in your procedure:
    CREATE PROCEDURE dbo.myProc
    @pkA int
    , @pkB varchar(10)
    , @pkC decimal(12, 2)

    AS
    SET NOCOUNT ON

    DECLARE @sql nvarchar(4000)

    SET @sql = N'
    SELECT
      t.colA, t.colB, t.colC
    FROM
      dbo.myTable t
    WHERE
      1 = 1
      @pkAREP
      @pkBREP
      @pkCREP'

    IF @pkA IS NOT NULL
      SET @sql = REPLACE(@sql, '@pkAREP', 'AND a.pkA = @pkA')

    ELSE
      SET
    @sql = REPLACE(@sql, '@pkAREP', '')

    IF @pkB IS NOT NULL
      SET @sql = REPLACE(@sql, '@pkBREP', 'AND a.pkB = @pkB')

    ELSE
      SET
    @sql = REPLACE(@sql, '@pkBREP', '')

    IF @pkC IS NOT NULL
      SET @sql = REPLACE(@sql, '@pkCREP', 'AND a.pkC = @pkC')

    ELSE
      SET
    @sql = REPLACE(@sql, '@pkCREP', '')


    EXECUTE "dbo"."sp_executesql"
      @sql
      , N'@pkA int, @pkB varchar(10), @pkC decimal(12, 0)'
      , @pkA = @pkA
      , @pkB = @pkB
      , @pkC = @pkC

    RETURN 0

I want to discuss option #4 above.  I believe it to do a number of things for the developer:

  1. Preventing SQL injection because you are using parameterized queries
  2. Caching query plans because you are using sp_executesql
  3. Controlling the data coming in to the procedure
  4. Preventing branching logic in your WHERE clause in the query

I actually would do a little more with the '1=1' part, but for clarity in this post I am not.  Also, in my queries I typically have values that are always required so the '1=1' isn't necessary.  Of course, as the developer you need to ensure that you don't inadvertantly put any sql injection points in your dynamic portions of your sql.  But this would get caught in a code review, right?  ;-)

Sponsor
Published Wednesday, September 08, 2004 2:02 PM by dpenton
Filed under:

Comments

 

dpenton said:

Hey... I like. Actually I love.

For me the coolest parts of your approach come down to two things:
1) Clarity. The factors are excellent. Anyone with a brain knows what's going on here.
2) The complete separation of the control channel and the data channel while still using dynamic sql.
Couple that with the fact that execution is performant/good and wow.

This is one form of dynamic sql I'd happily use without any remorse. (In fact I found myself thinking (because I still worry a good deal about System.Data.SqlServer abuses in Yukon) that this method of using sp_executesql with explicit parameters against a replaced string will easily be at home as a best-practice in the framework.... performance is solid, factors will actually be better in .NET languages, and the security is rock-solid.)

Very cool stuff. Thanks for posting.
September 8, 2004 9:33 PM
 

dpenton said:

wow. I have an awful lot of AND's, OR's and ISNULL's in my procedures; i would never have considered this if it wasn't you saying it!

I'll add this to my list of refactoring to be done soon.

Thanks!
February 14, 2005 1:12 PM
 

dpenton said:

What in your opinion would be wrong with the following, assuming all the pararmeters from above

select t.ColA, t.ColB, t.ColC from dbo.MyTable t
where
t.ColA = IsNull (@pkA, t.ColA )
and t.ColB = IsNull( @pkB, t.ColB)
and t.ColC = IsNull( @pkC, t.ColC)

This approach captures the same logic, but in a straight forward query without the need for dynamic sql.
February 14, 2005 4:34 PM
 

TrackBack said:

Something evil is afoot in Gotham City...
December 3, 2004 8:06 PM
Anonymous comments are disabled