title: Applying a WHERE clause conditionally in an SQL query
body: <p>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.  </p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Given that I can't change the signature of the procedure, how can I rewrite this so it doesn't require two separate queries?</p>
  
  <p>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.</p>
  
  <p>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"</p>
  
  <p>How can I accomplish that in a single query (and please no dynamic SQL suggestions)?</p>