Database Programming: One Last Gasp On Optional Parameters

re: Database Programming: One Last Gasp On Optional Parameters

Adam Machanic — Thu, 13 Jul 2006 06:05:56 GMT

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

Ward Pond — Thu, 13 Jul 2006 06:31:00 GMT

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

Thu, 13 Jul 2006 06:37:24 GMT

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

Thu, 13 Jul 2006 06:40:29 GMT

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

Ward Pond's SQL Server blog — Fri, 21 Jul 2006 10:33:46 GMT

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

dave^2=-1 — Fri, 01 Feb 2008 13:23:13 GMT

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