SQL Server -- TOP vs ROWCOUNT (a partial explanation)

I learned something new today...I love it when that happens. A friend of mine was trying to figure out how to accomplish something like the following via TSQL (I will use the SQL Server pubs database for simplicity sake):

USE pubs

GO

DECLARE @TOP INT

SET @TOP = 20

SELECT TOP @TOP fname, lname FROM employee

--etc

Query Analyzer will generate an error similar to:

Server: Msg 170, Level 15, State 1, Line 5

Line 5: Incorrect syntax near '@TOP'.

It would appear that you cannot use a variable for the parameter of the 'TOP' keyword. So, how to work around this? With a little bit of investigating/playing around/trial and error, I discovered that the same desired end result can be accomplished using the ROWCOUNT keyword. Here is an example:

USE pubs

GO

DECLARE @RowCount INT

SET @RowCount = 20

SET ROWCOUNT @RowCount

SELECT fname, lname

FROM employee

Voila, you will only get the first 20 rows returned from the query, the ROWCOUNT keyword is quite happy taking in a variable as its parameter.

No comments yet