Database Programming: One Last Gasp On Optional Parameters


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 (6)

  1. Anonymous says:

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

  2. Anonymous says:

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

  3. Adam Machanic says:

    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

  4. Ward Pond says:

    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.