title: Stored procedure: Select by matching input parameter or return the null records
body: <p>I have the following case:</p>
  
  <p>There is a table like this: </p>
  
  <pre><code>Id     | Param  | Value
  ------ | ------ | -------------
  1      | 1      | One 1
  1      | NULL   | Null-Value 1
  1      | 2      | Two 1
  1      | 3      | Three 3
  2      | NULL   | Nul-Value 2
  2      | 2      | Two 2
  3      | NULL   | Null-Value 3
  4      | 1      | One 4
  5      | NULL   | Null-Vaue  5
  6      | NULL   | Null-Value 6
  </code></pre>
  
  <p>I have to write a stored procedure as by given input nullable parameter for "Param" I have to generate a result which will contain a table with ID and Value and the result is based on the logic - </p>
  
  <p><strong>If the input parameter is null - return all rows with null values for Param</strong> </p>
  
  <p><strong>If the parameter is not null, then return the rows which match that parameter in the Param column and also all rows (for the other IDs), which have null value as Param.</strong> </p>
  
  <p><strong>There MUST be only ONE result per Id</strong>.</p>
  
  <p><em>Consider that there is applied an unique composite index on the Id and Param columns.</em>   </p>
  
  <p><strong>Example:</strong></p>
  
  <pre><code>Input parameter: 1
  
  Output table:
  
  Id | Value
  -- | -------------
  1  | One 1
  2  | Nul-Value 2
  3  | Null-Value 3
  4  | One 4
  5  | Null-Vaue  5
  6  | Null-Value 6
  </code></pre>
  
  <p><strong>Example 2 (let me include the Param column as well for better visibility):</strong></p>
  
  <pre><code>Input parameter: 2
  
  Output table:
  
  Id | Param | Value
  -- | ----- |-------------
  1  | 2     |Two 1
  2  | 2     |Two 2
  3  | NULL  |Null-Value 3
  5  | NULL  |Null-Value 5
  6  | NULL  |Null-Value 6
  </code></pre>
  
  <hr>
  
  <ul>
  <li>I suppose it will be a join of the table with itself, or even better with cross(or maybe outer) apply and some proper where clause...</li>
  </ul>