Filter stored procedure resultset with ISNULL

Often developers need to write a stored procedure that returns a resultset filtered based on input parameters. One solution is to use the EXECUTE command passing a SQL strings made by concatenating a SELECT statement with a WHERE clause. The EXECUTE command does the job but it is limited in the following areas:

• Patching complex conditions in the WHERE clause can get cumbersome.
• The SQL string passed to the EXECUTE command runs in its own memory space, hence it doesn’t share the same settings used by the main batch.
• The server cannot optimize the patched SQL like it does regular batches.

One solution to replace the EXECUTE command is to use the ISNULL function. The following stored procedure queries the orders table and returns a resultset based on the parameters provided. If null is passed in the parameter, the procedure will ignore it:

orders Table

USE orders_database

CREATE PROCEDURE [DBO].[order_filter]
@name CHAR(10),
@price INT
AS
SELECT *
FROM orders
WHERE name = ISNULL(@name, name)
AND price = ISNULL(@price, price)

Run this SQL script from the SQL window in Teratrax Database Manager to create the stored procedure. Replace every thing in lower case with your own.

Try running this procedure in the following scenarios (replace parameters to match your own criteria):

EXECUTE order_filter 'John Doe', null
EXECUTE order_filter null, 400
EXECUTE order_filter null, null