Monday, August 10, 2009

How to add optional filter parameters to Stored Procedure

Sometimes you need to use optional parameters to filter results of select or other statement in stored procedure. Here it goes example I think it is self explaining...
--
-- Adding optional filter parameters to Stored Procedure
-- GUID of this code snippet: 80901808-027c-47a8-81de-472d58281392
-- [ extends code snippet: None ]
-- Author: Darius Kucinskas (c) 2008-2009
-- Email: d[dot]kucinskas[eta]gmail[dot]com
-- Blog: http://blog-of-darius.blogspot.com/
-- License: GPL
--
CREATE PROCEDURE ShapeReport
(
@min_width int = NULL,
@max_width int = NULL,
@min_height int = NULL,
@max_height int = NULL,
@type int = NULL
)
AS
SELECT type, count(*) [count] 
FROM shape 
WHERE (@min_width IS NULL OR (width >= @min_width)) 
AND (@max_width IS NULL OR (width <= @max_width)) 
AND (@min_height IS NULL OR (height >= @min_height))
AND (@max_height IS NULL OR (height <= @max_height))
AND (@type IS NULL OR (type = @type))
GROUP BY type
HAVING COUNT(*) >= 1 
ORDER BY [count] DESC