Gail Shaw, who is a SQL MVP from South Africa, has an excellent blog post about this subject.

The executive summary is that having lots of conditional logic in a stored procedure tends to confuse the SQL query optimizer and it often causes problems with parameter sniffing. Parameter sniffing is when SQL Server uses the values passed in as input parameters to come up with a query plan appropriate for the values that were passed in the first time the SP is executed. This plan is cached, and reused whenever the SP is called (until something happens to remove it from the cache).

The problem is that a query plan (such as an index seek) that is appropriate when very few rows are returned, is not appropriate when many rows will be returned (where an index scan would be better). Writing lots of conditional logic in your stored procedures greatly increases the chances of having performance problems.

One easy solution is to have separate sub-procedures that are executed based on the results of your conditional logic in a "parent" stored procedure or in your C# code.  Basically, I try to discourage people from putting to much conditional logic in stored procedures, since I firmly believe that you should do as much of that as possible in your middle-tier code.

Relational databases are like big, dumb, faithful, hard working animals. When a large, mission critical database has performance or scalability problems, it affects your entire application or company.  Asking the database to do complicated things is usually asking for trouble.