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

• Anonymous
  January 01, 2003
  PingBack from http://blogs.technet.com/wardpond/archive/2006/07/10/441050.aspx
• Anonymous
  January 01, 2003
  When I first published Pond’s Laws, I promised it would be a living document.  Herewith is the first evidence.
• Anonymous
  January 01, 2003
  The comment has been removed
• Anonymous
  January 01, 2003
  PingBack from http://blogs.technet.com/wardpond/archive/2006/07/12/441395.aspx
• Anonymous
  July 12, 2006
  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
  ---
• Anonymous
  July 12, 2006
  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.