# How to compare case statements in where caluse and pass the parementer value it its false. [Answered]RSS

## 4 replies

Last post Dec 01, 2014 04:15 AM by Michelle Ge - MSFT

Member

58 Points

289 Posts

### How to compare case statements in where caluse and pass the parementer value it its false.

How to compare  case statements in where caluse and pass the parementer value it its false.

Declare @Empno int
set @Empno=-1
select * from emp where
(case when Empno=-1 then -1 else -1 end)=(case when Empno=@Empno then @Empno else Empno end)

if two side -1 =-1 then pass where cluase nothing
if <> then pass @Empno value

Contributor

6612 Points

1744 Posts

### Re: How to compare case statements in where caluse and pass the parementer value it its false.

```declare @emp table (Empno int, name varchar(50))

insert into @emp values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e')

Declare @Empno int
set @Empno=-1

--1. as your requirement nothing display
select * from @emp where
empno =
case
when @Empno = -1 then -1 --here give the value which does not exist in table, -1 or 9999999 or anything
else @empno
end
--(case when Empno=-1 then -1 else -1 end)=(case when Empno=@Empno then @Empno else Empno end)

--2. suppose you want display all record when @empno = -1, so logic will be
select * from @emp where
empno = case when @Empno = -1 then empno else @empno end

set @Empno = 1

--3. for second
select * from @emp where
empno = case when @Empno = -1 then empno else @empno end```

• ### mbanavige

All-Star

154817 Points

13201 Posts

ASPInsiders

Moderator

### Re: How to compare case statements in where caluse and pass the parementer value it its false.

`select * from emp where (Empno = @Empno) OR (@Empno = -1)`

Mike Banavige

Member

58 Points

289 Posts

### Re: How to compare case statements in where caluse and pass the parementer value it its false.

Hi Ajay,

When I tried this getting error

declare @emp table (Empno int, name varchar(50))

insert into @emp values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e')

Declare @Empno int
set @Empno=-1

--1. as your requirement nothing display
select * from @emp where
empno =
--case
--    when @Empno = -1 then -1 --here give the value which does not exist in table, -1 or 9999999 or anything
--    else @empno
--end
(case when Empno=-1 then -1 else -1 end)=(case when Empno=@Empno then @Empno else Empno end)

All-Star

18243 Points

2200 Posts

### Re: How to compare case statements in where caluse and pass the parementer value it its false.

Dec 01, 2014 04:15 AM|Michelle Ge - MSFT|LINK

#### SSK_Nani

(case when Empno=-1 then -1 else -1 end)=(case when Empno=@Empno then @Empno else Empno end)

Hi,

According to your description and the sql query you provided, as the @Empno=-1 and Empno=-1 then your where condition will be:

`where empno=-1=-1`

So your sql query is not correct, please refer to the sql query below:

```SELECT *
FROM @emp
WHERE Empno=
CASE WHEN @Empno=-1 then @Empno ELSE Empno END```