How to Short-circuit Where Clauses

Total article views: 3935 | Views in the last 30 days: 32

 Rate this |   Join the discussion |  Briefcase

There are many times when you might be concatenating text in a stored procedure to make a where clause, because the input parameters are optional. An alternative is to create many versions of the stored procedure - one for each possible combination of parameters - which can soon become too big. There is a more viable alternative - use "lazy evaluation". In the following script, @Input mimics an input parameter. If it is the default (-1) then all records are returned, while other values of @Input are added to the where clause as normal.

Total article views: 3935 | Views in the last 30 days: 32