CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Jim
Jim

Reputation: 6881

Applying a WHERE clause conditionally in an SQL query

I have a pre-existing procedure that I need to modify, and in a very simplified manner, it's written similar to the example below.

The procedure gets passed in a @StudentID and a @SubjectID, and if the @SubjectID = 0, it executes the query without filtering on SubjectID at all, otherwise it executes the exact same query but filters on SubjectID.

CREATE PROCEDURE SomeProcedure
  @StudentID INT,   
  @SubjectID INT    
AS
BEGIN   
  IF(@SubjectID = 0)
  BEGIN  
    SELECT SomeColumns
    FROM SomeTable
    WHERE StudentID = @StudentID
  END 
  BEGIN  
    SELECT SomeColumns
    FROM SomeTable
    WHERE StudentID = @StudentID
    AND SubjectID = @SubjectID
  END 
END

This seems ridiculous to me; we now have to maintain two copies of the same query; one that filters on SubjectID and one that doesn't. I don't have control over the fact that the application expects to pass SubjectID = 0 to get results for all subjects.

Given that I can't change the signature of the procedure, how can I rewrite this so it doesn't require two separate queries?

I've tried using a CASE statement in the WHERE clause, but I can't figure out how to conditionally include parts of the WHERE predicate.

The english translation of this problem is "IF @SubjectID = 0, return results with any SomeTable.SubjectID value, ELSE only return results where SomeTable.SubjectID = @SubjectID"

How can I accomplish that in a single query (and please no dynamic SQL suggestions)?

Upvotes: 2

Views: 357

Answers (1)

Lamak
Lamak

Reputation: 70638

Though I don't see anything really wrong with having two separate queries, if you want to use only one query, then you can do the following:

SELECT SomeColumns
FROM SomeTable
WHERE StudentID = @StudentID
AND (SubjectID = @SubjectID OR @SubjectID = 0)

Upvotes: 6

Related Questions