Great Post on SQL Server Stored Procedure Variables

It took me a while to find this about using variables in the TOP clause of a MSSQL stored prodedure. This post answered all my questions:

http://sqlserver2000.databases.aspfaq.com/how-do-i-use-a-variable-in-a-top-clause-in-sql-server.html

Here's the best part


CREATE PROCEDURE dbo.getFoo
  @top INT 
AS
  BEGIN
    SET ROWCOUNT @top

    SELECT foo
      FROM blat
      ORDER BY foo DESC

    -- never forget to set it back to 0!
    SET ROWCOUNT 0
  END
GO