Improving Performance Of Search Queries

I have a number of complex search stored procedures that use the following syntax to try to simplify the code.

WHERE @SearchParam IS NULL OR SearchCol = @SearchParam

unfortunately it appears that this is really inefficient as far as the database is concerned.

If I run the following query on the AdventureWorks database (SQL Server 2005 with SP2 and fixes up to v3054)




Code Block
SET STATISTICS IO ON

Declare @CustomerID int
SET @CustomerID = 1

SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE @CustomerID IS NULL OR CustomerID = @CustomerID

SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID






Is see that the first select results in 45 logical reads, whereas the second results in only 2 logical reads.

Does anyone have any idea how I can get the benefit of a search procedure that does not have loads of IF blocks, or dynamic SQL without this major performance issue?

Cheers,

Justin