Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: One Last Gasp On Optional Parameters

Database Programming: One Last Gasp On Optional Parameters

  • Comments 6
  • Likes

July 12 update: Everything that follows is WRONG.  See Adam's comment and my follow-up.

You might think I'm obsessing over this, and you may well be right, but I wanted to make one more comment about handling optional parameters.

The issue with the syntax I initially proposed was that it didn't handle nullity in the database properly.  If any of the tested columns are NULLable, then, as far as our current research is concerned, dynamic SQL is the one, true path.

However, if all of the columns being tested in the database are declared NOT NULL, but the parameters themselves are NULLable, then I believe a modified version of my proposed syntax will work:

SELECT  x
FROM    dbo.
foo
WHERE   y1 = COALESCE(@p1, y1
)
AND
     y2 = COALESCE(@p2, y2
)
AND
     y3 = COALESCE(@p3, y3
)
AND
     y4 = COALESCE(@p4, y4)

This produces identical results (and an identical query plan) to dynamic SQL if the conditions above are met.  It's certainly a niche application rather than the bold generic statement that I initially hoped to make, but if I'm correct (Adam? Tony?) then I will at least have salvaged a small shred of this work..  and my dignity.  :)

Comments are encouraged, as always.  Thanks for tolerating my focus on this issue.

     -wp

Comments
  • I hope you're joking, Ward?

    ---
    use tempdb
    go

    select
    y1.number as y1,
    y2.number as y2
    into wardstable
    from
    master..spt_values y1,
    master..spt_values y2
    where
    y1.type = 'P' and y1.number <= 500
    and y2.type = 'P' and y2.number <= 500
    go

    create clustered index y1 on wardstable (y1)
    create nonclustered index y2 on wardstable (y2)
    go

    create procedure GetWardsNumber
    @y1 int = null,
    @y2 int = null
    as
    begin
    set nocount on

    select *
    from wardstable
    where
    y1 = coalesce(@y1, y1)
    and y2 = coalesce(@y2, y2)
    end
    go

    create procedure GetAdamsNumber
    @y1 int = null,
    @y2 int = null
    as
    begin
    set nocount on

    declare @sql nvarchar(max)
    set @sql =
    'select * ' +
    'from wardstable ' +
    'where 1=1 ' +
    case
    when @y1 is not null then
    'and y1 = @y1 '
    else ''
    end +
    case
    when @y2 is not null then
    'and y2 = @y2 '
    else ''
    end

    exec sp_executesql
    @sql,
    N'@y1 int, @y2 int',
    @y1,
    @y2
    end
    go


    set statistics io on
    go

    exec getwardsnumber @y1 = 1
    go

    exec getwardsnumber @y2 = 2
    go

    exec getadamsnumber @y1 = 1
    go

    exec getadamsnumber @y2 = 2
    go

    set statistics io off
    go

    drop proc getadamsnumber
    go
    drop proc getwardsnumber
    go
    drop table wardstable
    go
    ---

  • Boy, Adam, I can sure see why you ask the question.

    Readers, Adam's syntax in the sample generates 6 logical reads while mine generates 594.

    I hereby renounce all forms of the COALESCE syntax, and furthermore promise not to write any more performance-related blog posts while I'm packing for a vacation.

    Thanks, Adam, for setting me straight.

  • PingBack from http://blogs.technet.com/wardpond/archive/2006/07/12/441395.aspx

  • PingBack from http://blogs.technet.com/wardpond/archive/2006/07/10/441050.aspx

  • When I first published Pond’s Laws, I promised it would be a living document.  Herewith is the first evidence.

  • There are several common methods for using dynamic search conditions (a.k.a. optional parameters) in

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment