Listing 5: Script That Creates Version 3 of the Stored Procedure
ALTER PROC dbo.usp_GetOrders
  @OrderID    AS INT      = NULL,
  @CustomerID AS NCHAR(5) = NULL,
  @EmployeeID AS INT      = NULL,
  @OrderDate  AS DATETIME = NULL
AS

DECLARE @sql AS NVARCHAR(4000);

SET @sql = 
    N'SELECT OrderID, CustomerID, EmployeeID, OrderDate, filler'
  + N' FROM dbo.Orders'
  + N' WHERE 1 = 1'
  + CASE WHEN @OrderID IS NOT NULL THEN
      N' AND OrderID = @oid' ELSE N'' END
  + CASE WHEN @CustomerID IS NOT NULL THEN
      N' AND CustomerID = @cid' ELSE N'' END
  + CASE WHEN @EmployeeID IS NOT NULL THEN
      N' AND EmployeeID = @eid' ELSE N'' END
  + CASE WHEN @OrderDate IS NOT NULL THEN
      N' AND OrderDate = @dt' ELSE N'' END;

EXEC sp_executesql
  @sql,
  N'@oid AS INT, @cid AS NCHAR(5), @eid AS INT, @dt AS DATETIME',
  @oid = @OrderID,
  @cid = @CustomerID,
  @eid = @EmployeeID,
  @dt  = @OrderDate;
GO