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