Suppose you need to exclude or include parameters to a sql statement.  Let's take this example:

CREATE PROCEDURE dbo.myProc
    @pkA int
    , @pkB varchar(10)
    , @pkC decimal(12, 2)

AS

SELECT
    t.colA, t.colB, t.colC
FROM
    dbo.myTable t
WHERE
    t.pkA = @pkA
    AND t.pkB = @pkB
    AND t.pkC = @pkC

This procedure expects three parameters, as does the sql statement.  So we want to omit the parameters based on being set to NULL.  There are several ways to do this:

1. Use dynamic sql in your client language
2. Use dynamic sql in your stored procedure
3. Use conditional logic in your query with various combinations of OR, AND or COALESCE/ISNULL in your query
4. Use structured dynamic sql in a stored procedure

Let me give an example of each:

1. Build the sql in a string such as this:
   
   string sql = “SELECT colA FROM tblA“;
   if(prmB != null && prmB.Length != 0) sql += “ WHERE colB = '“ + prmB.Replace(“'“, “''“) + “'“; 
   
   You have to contend with ensuring that your logic in the final query is correct.  This is easy with a single parameter, but when you end up having more than one in your WHERE clause (or other clauses) then the logic can get quite complex.  We also needed to deal with SQL Injection.  Of course, this is more easily dealt with by using parameters on command objects (like using a SqlParameter with a SqlCommand object). 
2. This is similar to the above but you are doing the logic in the stored procedure.  You still need to contend with sql injection in this case.
3. Conditional logic in your query means resorting to using OR and AND when needed:
   
   SELECT colA FROM tblA
   WHERE (colB = @colB OR @colB IS NULL)
   
   -- OR --
   
   SELECT colA FROM tblA
   WHERE colB = COALESCE(@colB, colB)
   
   This suffers from branching logic in your query.  It becomes more an more difficult for SQL Server to make a realistic execution plan for this type of query, especially when more conditions are present.
4. Use structured dynamic sql in your procedure:

   CREATE PROCEDURE dbo.myProc
       @pkA int
       , @pkB varchar(10)
       , @pkC decimal(12, 2)
   
   AS
   SET NOCOUNT ON 
   
   DECLARE @sql nvarchar(4000) 
   
   SET @sql = N' 
   SELECT 
     t.colA, t.colB, t.colC 
   FROM 
     dbo.myTable t 
   WHERE 
     1 = 1 
     @pkAREP 
     @pkBREP 
     @pkCREP'
   
   IF @pkA IS NOT NULL
     SET @sql = REPLACE(@sql, '@pkAREP', 'AND a.pkA = @pkA')
   
   ELSE
     SET @sql = REPLACE(@sql, '@pkAREP', '')
   
   IF @pkB IS NOT NULL
     SET @sql = REPLACE(@sql, '@pkBREP', 'AND a.pkB = @pkB')
   
   ELSE
     SET @sql = REPLACE(@sql, '@pkBREP', '')
   
   IF @pkC IS NOT NULL
     SET @sql = REPLACE(@sql, '@pkCREP', 'AND a.pkC = @pkC')
   
   ELSE
     SET @sql = REPLACE(@sql, '@pkCREP', '')
   
   
   EXECUTE "dbo"."sp_executesql" 
     @sql 
     , N'@pkA int, @pkB varchar(10), @pkC decimal(12, 0)' 
     , @pkA = @pkA 
     , @pkB = @pkB 
     , @pkC = @pkC 
   
   RETURN 0
   

I want to discuss option #4 above.  I believe it to do a number of things for the developer:

1. Preventing SQL injection because you are using parameterized queries
2. Caching query plans because you are using sp_executesql
3. Controlling the data coming in to the procedure
4. Preventing branching logic in your WHERE clause in the query

I actually would do a little more with the '1=1' part, but for clarity in this post I am not.  Also, in my queries I typically have values that are always required so the '1=1' isn't necessary.  Of course, as the developer you need to ensure that you don't inadvertantly put any sql injection points in your dynamic portions of your sql.  But this would get caught in a code review, right?  ;-)