Jeff Smith Blog

use Northwind

create procedure Test1 @MinDate datetime = null, @MaxDate datetime = null,
    @CustomerID nchar(5) = null,
    @FreightMin money = null, @FreightMax money = null
as
begin
    set @MinDate = isnull(@MinDate,'1/1/1900')
    set @MaxDate = isnull(@MaxDate,'12/31/2999')
    set @CustomerID = isnull(@CustomerID,'%')
    set @FreightMin = isnull(@FreightMin,-99999)
    set @FreightMax = isnull(@FreightMax,99999)

select *
from Orders
where
    OrderDate between @MinDate and @MaxDate and
    CustomerID like @CustomerID and
        Freight between @FreightMin and @FreightMax
end

go

create procedure Test2
    @MinDate datetime = null, @MaxDate datetime = null,
    @CustomerID nchar(5) = null,
    @FreightMin money = null, @FreightMax money = null
as
begin
select * from Orders where
  (@MinDate is null or OrderDate >= @MinDate) and
  (@MaxDate is null or OrderDate <= @MaxDate) and
  (@CustomerID is null or CustomerID = @CustomerID) and
  (@FreightMin is null or Freight >= @FreightMin) and
  (@FreightMax is null or Freight <= @FreightMax)
end

go

– test it out:

exec test1 '2/1/1996','9/20/1996', N'BERGS'
exec test2 '2/1/1996','9/20/1996', N'BERGS'

exec test1
exec test2

exec test1 @FreightMin=44, @FreightMax=48
exec test2 @FreightMin=44, @FreightMax=48