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:
CREATE PROCEDURE [DBO].[order_filter]
WHERE name = ISNULL(@name, name)
AND price = ISNULL(@price, price)
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