Welcome to TechNet Blogs Sign in | Join | Help

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

Published Monday, July 10, 2006 8:00 PM by Ward Pond

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# re: Database Programming: One Last Gasp On Optional Parameters

Wednesday, July 12, 2006 11:05 PM by Adam Machanic
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
---

# re: Database Programming: One Last Gasp On Optional Parameters

Wednesday, July 12, 2006 11:31 PM by Ward Pond
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.

# Ward Pond's SQL Server blog : Optional Parameters: Adam Hopes I'm Joking, But The Joke's On Me

Wednesday, July 12, 2006 11:37 PM by Ward Pond's SQL Server blog : Optional Parameters: Adam Hopes I'm Joking, But The Joke's On Me
PingBack from http://blogs.technet.com/wardpond/archive/2006/07/12/441395.aspx

# Ward Pond's SQL Server blog : Database Programming: One Last Gasp On Optional Parameters

Wednesday, July 12, 2006 11:40 PM by Ward Pond's SQL Server blog : Database Programming: One Last Gasp On Optional Parameters
PingBack from http://blogs.technet.com/wardpond/archive/2006/07/10/441050.aspx

# Pond’s Eleventh Law Emerges From Reflection Upon The Perils Of Going Fast

Friday, July 21, 2006 3:33 AM by Ward Pond's SQL Server blog
When I first published Pond’s Laws, I promised it would be a living document.  Herewith is the first evidence.

# Everything about dynamic search conditions in T-SQL

Friday, February 01, 2008 5:23 AM by dave^2=-1

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

Leave a Comment

(required) 
required 
(required) 

  
Enter Code Here: Required
 
Page view tracker