The following Stored Procedure example demonstrates how optional predicates can be used in one statement based upon the parameters that are passed to the Stored Procedure. The only caveat is that the columns cannot be NULLABLE as the predicate will not be evaluated correctly when a value is NULL, as NULL is not equivalent to NULL. For more details regarding the joys of NULL’s refer to http://www.sqlservercentral.com/columnists/pward/malefemaleandtheotheronenull.asp.
CREATE PROC dbo.usp_Orders
@CustomerID NCHAR(10) = NULL,
@EmployeeID INT = NULL
AS
SET NOCOUNT ON
SELECT OrderID
FROM Northwind.dbo.Orders
WHERE CustomerID = COALESCE(@CustomerID, CustomerID)
AND EmployeeID = COALESCE(@EmployeeID, EmployeeID)
GO
In this example the Stored Procedure can be called in one of three ways
ie.
EXEC dbo.usp_Orders @EmployeeID = 1
EXEC dbo.usp_Orders @CustomerID = ‘VINET’
EXEC dbo.usp_Orders @CustomerID = ‘VINET’, @EmployeeID = 5
