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)

Share

Share

Rate

3.75 (4)