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
"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:
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........
Post a Comment