title: SQL: How To Conditionally Adjust the WHERE Clause Depending on Passed Parameters and still work with UNION
body: <p>I'm fairly inexperienced with SQL. I'm using SQL Server 2012.</p>
  
  <p>Basically I want the WHERE clause of a SQL SELECT statement to be adjusted depending on the values of certain parameters passed to the stored procedure.</p>
  
  <p>I'll also be UNIONing the result of this SELECT with another SELECT statement within the stored procedure.</p>
  
  <p>The structure so far is as follows:</p>
  
  <pre><code>CREATE PROCEDURE dbo.procMyProcedure
    @Param1 smallint = null
    , @Param2 int = null
    , ...
  AS ...
  
  --I have three separate SELECT statements, each catering for a specific scenario
  
      SELECT ...
      FROM ...
      WHERE
          (ER.EMR_Number = ISNULL(@Param1, ER.EMR_Number)) AND 
          (EE.EME_Number = ISNULL(@Param2, EE.EME_Number))
  
      SELECT ...
      FROM ...
      WHERE
          (EE.EME_Number = ISNULL(@Param2, EE.EME_Number))
  
      SELECT ...
      FROM ...
      WHERE
          (ER.EMR_Number = ISNULL(@Param1, ER.EMR_Number))
  </code></pre>
  
  <p>The statements are identical except for the WHERE clause. In my scenario, the first statement returns exactly 1 result. The second returns 119 results. The third returns 3 results.</p>
  
  <p>I don't want to run all three statements. I want to merge them into one statement, with that single statement returning the same results as the three statements above, depending on the values of the passed (optional) parameters.</p>
  
  <p>I can do this by wrapping the SELECTs in a nested IF ... ELSE IF structure, but then I can't use UNION. The following structure doesn't work:</p>
  
  <pre><code>    IF ...
          SELECT ...
      ELSE IF ...
          SELECT ...
      ELSE IF ...
          SELECT ...
  UNION
      SELECT ...
  </code></pre>
  
  <p>There must be a way to merge these into a single SELECT statement, in such a way that I can use it as part of a UNION statement.</p>
  
  <p>I want to get to the following:</p>
  
  <pre><code>SELECT ... --This select
  UNION
  SELECT ... --Other stuff
  </code></pre>
  
  <p>I'd really appreciate any help with this.</p>
  
  <p>Regards,</p>
  
  <p>Adam.</p>