Using Variables in TOP clause in T-SQL

This is what I want to post as I heard so many people are facing this issue. We can use variables in T-SQL queries but, there are requirements that we may select only the top 5 rows or 10 rows or n rows from the result set and use them wherever needed. So, below is the implementation we have to use to achieve that.

1. One way could be using a variable, which saves the complete t-sql query in it and execute that total query.
2. Second way will be pass the variable dynamically to TOP clause.

I do not think the first way is feasible. So, I prefer to go to second way and below are the implementation details.

SQL SERVER 2005:

DECLARE @TOP INT;
SET @TOP = 10;
SELECT TOP (@TOP) * FROM [User]

If you observe the variable is what storing the value of how many values we need to select. from the table And the last statement is what the final query which does what we needed. Remember the brackets () around the @TOP variable are what must and should. I also failed to write the query very first time as I forgot to place the brackets around the @TOP variable.

Earlier Versions: SQL SERVER 2000:

DECLARE @TOP INT;
SET @TOP = 5;

SET ROWCOUNT @TOP;
SELECT * FROM [User];
SET ROWCOUNT 0; --DO NOT FORGET TO WRITE THIS. VERY IMPORTANT

There is nothing tricky here. Just setting the ROWCOUNT internal variable to the required value is what works for us. And the very important thing is do not forget to reset the value of ROWCOUNT back to 0. Otherwise it effects the other result sets which comes after these T-SQL statements.

I think you got what I am trying to say here and enjoy the nice tips and posts.