Something I see often is difficulty in using “filters” in a stored procedure used to select – for example – products based on various different restrictions. Often I see people using dynamically written SQL in the code-layer and then sent to the SQL Server and EXEC.
However a different method of doing this is to utilize the logic of the OR functionality in the Stored Procedure.
Suppose you have a table containing products. These products have – for example – a weight column, height, length and other such physical attributes. Now suppose you want to select all products from this table, but also give the option to filtering the select further – say below a specific weight and/or below a specific length and so on.
This is doable the following way:
DECLARE @Weight INT = NULL
DECLARE @Length INT = NULL
DECLARE @Width INT = NULL
SELECT *
FROM Product
WHERE (@Weight IS NULL OR (ProductWeight <= @Weight))
AND (@Length IS NULL OR (ProductLength <= @Length))
AND (@Width IS NULL OR (ProductWidth <= @Width))
Note this is 2008 syntax, if using 2000 or 2005 remember you can’t assign value to a declaration in the same line.
Also if using it in a Stored Procedure, the declarations are usually input parameters – but just default them to (for example) NULL.
When running this, with the null assignment, you’ll get all rows out, because all rows will fulfill the first part of the OR logic.
However if changing one, or more, of the variables to an actual value, the SQL will filter based on those values, because – as the variables no longer hold the value “null”, the second part of the OR statement comes into play.
So suppose you have the following values instead:
DECLARE @Weight INT = 10
DECLARE @Length INT = 15
DECLARE @Width INT = 20
The select will only select out those product rows which have a Weight less than or equal to 10, a Length equal to or less than 15 and likewise with Width and 20.
Omit one value and you’ll not filter for that one either, so
DECLARE @Weight INT = NULL
DECLARE @Length INT = 10
DECLARE @Width INT = NULL
Will select all products which have a length less than or equal to 10 – regardless of Weight and Width.
Of course, me using <= is basically irrelevant, as any logical operation can be performed within the second part of the OR statement.
An inherit disadvantage to this type of procedure over the more specialized ones are the difficulty in utilizing indexes. Due to the OR nature of the procedure, you’ll be unable to utilize the indexes optimal, so it is naturally a concern which you must consider when using such approach.