Notice that if your table is big, you'll might have performance issues. Unfortunetly I don’t remember the technical explanation on way using the third option causes a none optimal query plan (but I have a feeling that others will be able to give us a very good explanation). In any case take a look at the small demo that I wrote using AdventureWorks database. If you’ll compare both query plans and statistics I/O, you’ll see that when you use if statement, you’ll get a better query plan.

create proc FindOrderDetail (@ProductID int)

as

select * from sales.SalesOrderDetail

where (ProductID = @ProductID or @ProductID is null)

go

create proc FindOrderDetail2 (@ProductID int)

as

if @ProductID is null

select * from sales.SalesOrderDetail

else

select * from sales.SalesOrderDetail where ProductID = @ProductID

go

set statistics io on

exec FindOrderDetail 10

exec FindOrderDetail2 10

Adi