DIR Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
HTML https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
DIR Return to: General
*****************************************************
#Post#: 3--------------------------------------------------
Where clause with case
By: srinivasma_exceldbp Date: January 31, 2014, 3:13 am
---------------------------------------------------------
/*
In our applications there are case statements in where checks
whether a parameter value is passed or not
Developer should be aware if the column value contains NULL
row is rejected, if ISNULL is not used along with case .
Best to use option 3 if NULL values are required
Have a look and provide feedback
*/
use tempdb
go
-- drop table a
create table a ( keyid int,keyname varchar(50))
insert into a values (1,'abcd')
insert into a values (2,'efgh')
insert into a values (3,'mnop')
insert into a values (4,'qrst')
insert into a values (NULL,'uvwx')
select top 1 * from a
create clustered index ix_a_keyid on a (keyid)
update statistics a with fullscan
--** 1. Returns 4 rows . NULL rejected
set nocount on
declare @keyid int = 0
select * from a
where keyid = case when @keyid > 0 then @keyid else keyid end
--** 2. Returns 5 Rows . NULL included . But ISNULL function
applied to keyid which may not use index
select * from a
where isnull(keyid,0) = case when @keyid > 0 then @keyid else
isnull(keyid,0) end
--** Returns 5 Rows . NULL included. This is the best method
select * from a
where ( keyid = @keyid ) or @keyid = 0
use tempdb
drop table a
*****************************************************