<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>

I would create separate queries for the parameter being available or not.
This will create simpler SQL, and the optimizer will do a better job.
Like this:
```
```
if (@parameter IS NULL) then begin
   select * from foo
end
else begin
   select * from foo where value = @parameter
end
```
```
In you have to many parameters to redesign like this, and you go for the dynamic sql solution, then also always use parameters, you might get bitten by the
SQL-Injection
bug.
A combination is also possible. The most likely used query/queries you code in full, and get precompiled. All other combinations are created dynamically.