title: Working with optional stored procedure parameters
body: <p>I am working on a stored procedure with several optional parameters.  Some of these parameters are single values and it's easy enough to use a WHERE clause like:</p>
  
  <pre><code>WHERE (@parameter IS NULL OR column = @parameter)
  </code></pre>
  
  <p>However, in some instances, the WHERE condition is more complicated:</p>
  
  <pre><code>WHERE (@NewGroupId IS NULL OR si.SiteId IN (SELECT gs.SiteId
  FROM [UtilityWeb].[dbo].[GroupSites] AS gs
  WHERE gs.GroupId = @NewGroupId))
  </code></pre>
  
  <p>When I uncomment these complicated WHERE clauses, the query execution time doubles and the execution plan becomes remarkably more complicated. While the execution plan doesn't bother me, doubling the execution time of a query is a definite problem.</p>
  
  <p>Is there a best practice or pattern that others have found for working with optional parameters in their stored procedures?</p>
  
  <p>Is this one of those instances where dynamic SQL would be a better solution?</p>