conditional where clause in sql 2012

Question

I have a query as below in my Stored Procedure: I set the @SUBCONDITION FROM MY CODE BEHIND.

declare @SUBCONDITION VARCHAR(100)= NULL
if(@SUBCONDITION='DEPT')
BEGIN
Select * from table1 where IDDEPT=1
END

ELSE IF (@SUBCONTION='GRADE')
BEGIN
SELECT * FROM TABLE1 WHERE IDGRADE=1
END

ELSE IF(@SUBCONDITION='SECTION')
BEGIN
SELECT * FROM TABLE1 WHERE IDSECTION=1
END

Everything works just fine. My question is whether I can do this in just one query??? Like using if or case or something in the where clause?

Answer 1

Yes, you can do this in one query. It would look like this:

Select *
from table1
where (@SUBCONDITION = 'DEPT' and IDDEPT = 1) or
      (@SUBCONTION = 'GRADE') and IDGRADE = 1) or
      (@SUBCONDITION = 'SECTION' and IDSECTION = 1)

Answer 2

Try this:

Select * from table1 
where @SUBCONDITION = 'DEPT' AND IDDEPT=1 OR 
    @SUBCONDITION = 'GRADE' AND IDGRADE=1
    @SUBCONDITION = 'SECTION' AND IDSECTION=1

Answer 3

Try this:

SELECT * 
FROM table1 
WHERE 1 = (CASE @SUBCONDITION 
                WHEN 'DEPT' THEN IDDEPT
                WHEN 'GRADE' THEN IDGRADE
                WHEN 'SECTION' THEN IDSECTION
                ELSE 0
           END);