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.

2 Responses to “Filter parameters in a Stored Procedure”

  1. your rss feed appears to not work, I’m not seeing any updates in my feed reader.

  2. This is a neat solution to an issue I’d come to think of as just a fact of life. If an OR/M such as LINQ to SQL is not an option, it has the potential to greatly simplify the DAL in a situation where you wish to use stored procedures and filter in the DB rather than retireve all data and filter in the application.

    However, there is a stumbling block. You wrote:

    “Of course, me using <= is basically irrelevant, as any logical operation can be performed within the second part of the OR statement."

    Whilst any logical operation can be performed, what do you think is the neatest way of achieving this. e.g. in the first call to the SPROC I may want to retrieve all Products with a length less than 10. However, the next query may want all products with a length greater than 15, or equal to 20. I can't think of a tidy way of specifying the operator to use.

    One solution I thought of would be to have a different variable for each operator, ie.

    @LengthLT INT = NULL
    @LengthEQ INT = NULL
    @LengthGT = NULL

    You would then supply the @LengthLT variable if you wanted Products with length less than your value, @LengthEQ for equal to etc. However, it makes the code start to look really messy as you then have a whole lot more variables to test.

    I realise I'm replying to a three year old post, but if you happen to have an idea up your sleeve, or someone else stumbles upon this, I'd love to have your thoughts on it.

    Thanks
    Simon

Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>