Technical Article

How to Short-circuit Where Clauses

,

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.

/** Create the test table **/create table TblTest
(
SupplierId int null
)
go
insert into TblTest (SupplierId) values (1)
insert into TblTest (SupplierId) values (2)
insert into TblTest (SupplierId) values (3)
insert into TblTest (SupplierId) values (4)

/** Test Short-Circuiting - mimic a stored procedure call**/
declare @Input int
set @Input = 2

select * FROM TblTest
where
CASE
    WHEN @Input = -1              THEN 1
    when SupplierId = @Input THEN 1
else 0
END = 1

Rate

3.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.75 (4)

You rated this post out of 5. Change rating