Monday, December 10, 2012

CASE Statement in where condition in sql server

we can use case statement in where condition based on our requirement as below :


select * from employee where eid=@eid

 AND 1 =
(
    CASE
        WHEN @periodfrom is not NULL and @periodto is not null
            AND ((PeriodFrom between @periodfrom and @periodto) or(periodTo between @periodfrom and @periodto) )
             THEN 1
        WHEN @periodfrom is  NULL or @periodto is null
             then 1
        WHEN @periodfrom ='' and  @periodto=''
             then 1
        WHEN @periodfrom ='' and  @periodto!=''
         and (periodTo between @periodfrom and @periodto)
             then 1
        WHEN @periodfrom !='' and  @periodto=''
         and (PeriodFrom between @periodfrom and @periodto)
             then 1
    END


If you are searching life partner. your searching end with kpmarriage.com. now kpmarriage.com offer free matrimonial website which offer free message, free chat, free view contact information. so register here : kpmarriage.com- Free matrimonial website