I attended a training by Itzik Ben Gan and we found that this technique worked the best for our data set.

-- Stored Procedure GetOrders,

-- Using Dynamic SQL

ALTER PROC dbo.GetOrders

@orderid AS INT = NULL,

@custid AS INT = NULL,

@empid AS INT = NULL,

@orderdate AS DATETIME = NULL

AS

DECLARE @sql AS NVARCHAR(1000);

SET @sql =

N'SELECT orderid, custid, empid, orderdate, filler /* 27702431-107C-478C-8157-6DFCECC148DD */'

+ N' FROM dbo.Orders'

+ N' WHERE 1 = 1'

+ CASE WHEN @orderid IS NOT NULL THEN

N' AND orderid = @oid' ELSE N'' END

+ CASE WHEN @custid IS NOT NULL THEN

N' AND custid = @cid' ELSE N'' END

+ CASE WHEN @empid IS NOT NULL THEN

N' AND empid = @eid' ELSE N'' END

+ CASE WHEN @orderdate IS NOT NULL THEN

N' AND orderdate = @dt' ELSE N'' END;

EXEC sp_executesql

@stmt = @sql,

@params = N'@oid AS INT, @cid AS INT, @eid AS INT, @dt AS DATETIME',

@oid = @orderid,

@cid = @custid,

@eid = @empid,

@dt = @orderdate;