Search This Blog & Web

Tuesday, March 31, 2009

Using Case in where clause utilizing parameter

Recently i have faced a problem about parameters usage in where clase. Problem statement is as?
"I have 2 parameters and all alow null values. How can i impliement this logic."

I got one solution that is as under:

For 2 parameters

@vparam1
@vparam2

if @vparam1 is not null and @vparam2 is not null
Select "column list..."
From "table..."
Where "columname" = @vparam1 and "columnname" = @vparam2
elseif @vparam1 is not null and @vparam2 is null
Select "column list..."
From "table..."
Where "columname" = @vparam1

elseif @vparam2 is not null and @vparam1 is null
Select "column list..."

From "table..."
Where "columname" = @vparam2
else
Select "column list..."
From "table..."


If any one have another solution please update me ...... in any version of SQL Server

1 comment:

Shamas Saeed said...

This is good way for a developer but I have a better solution take a look at this:

Declare @vparam1 varchar(100);
Declare @vparam2 int

Select column list ....
from table....
where
column1 like (Case when @vparam1 is not null then @vparam1 else '%%' end)
and (case when @vparam2 is null then 1 else column2 end)
=
(case when @vparam2 is null then 1 else @vparam2 end)

Try this one........