-1

Sample Table:

Table1 (Userid, Name, Salary, Rank)

Query:

create procedure Proc_SelectDetails
as
@Salary int
begin
     select * from Table1 where salary >= @Salary  
end

If @salary > 50000, check rank also (with salary) that should be greater than 6 else don't consider rank for comparison. just compare salary. How to add this in where clause with help of case statement.

Salman A
  • 262,204
  • 82
  • 430
  • 521
Pinky
  • 9
  • 5
  • Please add data set and explain better your logic. So you can write where case salary... then .... end – Joe Taras Oct 09 '18 at 12:27
  • 1
    In general it's much better to use AND/OR constructions in the WHERE clause, instead of case expressions. – jarlh Oct 09 '18 at 13:09

2 Answers2

3

This should do it:

SELECT *
FROM Table1 
WHERE Salary >= @Salary
AND (@Salary <= 50000 OR Rank > 6)
Salman A
  • 262,204
  • 82
  • 430
  • 521
0

I might missed the logic of the where case, anyways this query shows an example of case when in where if salary is grater than @Salary compares rank with salary. 

select * from Table1 where 
     case when salary >= @Salary 
        then case when rank >= 6 then 1 
        else 0 end
     else 0 end = 1
lukaszberwid
  • 1,097
  • 7
  • 19