I've used a twist on the above use of CASE for controlling the WHERE clause depending on the values of input parameters.  Something like:

SELECT *

FROM MyTable

WHERE Col1 =...

Since "views are essentially macros which are expanded by the query optimizer into derived tables" without the stored execution plan of a stored procedure, you could get around this by creating...

Like Yukas said, you could rewrite the SP as a UDF.

Another alternative would be to keep your SP, but revise it to use a table variable instead of a temp...

I've also heard that cursors are generally not fast, but for many of the things I've been writing lately, I've used cursors a lot.  An alternative I've come across is...

Profiler is a good tool, but what I use more often--especially with complex triggers is a custom log table, sort of like your own custom profiler.  Typically, I'll create the...