I use a lot of queries to drive reports and interactive search tools and the most common requirement of all my queries is optional parameters. I’ve used a ton of different methods to pull this off in the past, but I primarily use one approach when I build stored procedures that will be used with optional parameters that is efficient and easy to read.

-- //////////////////////////////////////////
-- Stored Procedure - Optional Params (NEW WAY)
-- //////////////////////////////////////////
CREATE PROCEDURE GetOrders
	@OrderNumber VARCHAR(10),
	@TrackingNumber VARCHAR(10)
AS
	SELECT * FROM SALES
	WHERE (@OrderNumber IS NULL OR @OrderNumber = '' OR ORDERNUMBER = @OrderNumber)
		AND (@TrackingNumber IS NULL OR @TrackingNumber = '' OR TRACKINGNUMBER = @TrackingNumber)



-- //////////////////////////////////////////////////
-- Stored Procedure - Optional Params - Dynamic SQL (OLD WAY)
-- //////////////////////////////////////////////////
CREATE PROCEDURE GetOrders
	@OrderNumber VARCHAR(10),
	@TrackingNumber VARCHAR(10)
AS
	DECLARE @STRSQL VARCHAR(200)
	SET @STRSQL = 'SELECT * FROM SALES WHERE 1=1 '

	IF @ROBINCODE <> ''
	BEGIN
		SET @STRSQL = @STRSQL + ' AND ORDERNUMBER = ''' + @OrderNumber + ''''
	END

	IF @DELTOCODE <> ''
	BEGIN
		SET @STRSQL = @STRSQL + ' AND TRACKINGNUMBER = ''' + @TrackingNumber + ''''
	END

EXECUTE(@STRSQL)

As you can see, the “New Way” has a lot less code and does not use dynamic T-SQL. In addition to checking for NULL parameter values, I also suggest you check for empty strings since text boxes normally return an empty string.

Now you have the choice of sending NULL or “” and the criteria will be skipped.