Tip: Using SQL Server 2005 Stored Procedure to return the Top N records

I had a problem today where I tried to make a stored procedure to return the top N records from a table, where N was a parameter input to the stored procedure.
Apparently you can't say 'SELECT TOP @N..." where @N is the parameter.
A quick search led me to this article by 4GuysFromRolla which re-introduced me to the ROWCOUNT statement and showed how to use it most effectively in this case by saying 'SET ROWCOUNT @N" and then calling my SELECT statement to achieve the same effect.

The initial alternative a friend recommended was to dynamically build the SQL statement using @N as a parameter in the string being built and then calling EXEC on it, however dynamic SQL is something I was taught to stay away from unless absolutely necessary. This is because by its nature dynamically generated SQL would not have benefitted from SQL Server's built in query optimizer when the stored procedure's code was compiled and thus would run slower than fully declared SQL statements and stored procedures every single time it was called.