While I agree with everything that Jeff and Gail said, I wanted to add to a couple of these:

If @@ROWCOUNT is already set then you should use it. Otherwise, use EXISTS() as Gail pointed out.

The general thinking is to prefer derived tables because they leave the optimizer with more freedom to optimize. However, experience also shows that the more complex the query, the less likely the optimizer is going to make the best choices. Consequently, for significantly complex queries, converting one or more derived tables to temporary tables serves both as a way to force certain decisions on the optimizer and as a way to simplify the query, thus allowing the optimizer to make better decisions with what remains. The catch here is that if you convert the wrong derived table to a temporary table, then you end up forcing a bad choice on the optimizer, thus making the overall performance worse instead of better.

Static is preferred because it is safer, easier to read and write and (marginally) faster. However, some parametrized queries cannot be done as static SQL and many more can only be done in a terribly inefficient way. In these cases you should use dynamic SQL instead, but with great caution to avoid the possibility of Injection.