CODEWITHSUNDEEP
sql-server-2005
Venkateswarlu Avula
Venkateswarlu Avula

Reputation: 441

using IF and else in Where clause and using IN ( in where condition field)

I want to run the following query. @city value will be provided run time 

Select * from Emp
    where empno in (
        if( @city = 'Bangalore')
            Begin
                Select Empno from Emp
                    where EmpCity = @city
            End
        Else
            Begin
                Select empno from Emp
        End
    )

Kindly help me by providing alternative

Upvotes: 0

Views: 514

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

IF is a control flow statement. You can't place statements inside other statements.

The usual thing people are looking for when they come up with IF is CASE, which is an expression. However, in this case, a far simpler query seems doable:

Select * from Emp
where
    (EmpCity = @city or @city != 'Bangalore') and
    (EmpSalary = @salary or @salary != 20000) and
    (Designation = @designation or @designation != 'PM')

I don't see why you have the nested select at all

Upvotes: 1

Ram Mourya
Ram Mourya

Reputation: 2548

suppose your search is based upon EmpId -- @EmpId and City -- @city

Select * 
from 
  Emp 
where 
 (@city  = '' or EmpCity = @city) and
 (@EmpId = '' or EmpId = @EmpId)

Upvotes: 1

Related Questions