Today I was trying to select a predetermined number of rows in a table. I tried passing a parameter to the TOP operator.. Syntax Error.

SELECT TOP @n ID FROM TableName

After a small research I found two solutions to this problem.

First, the SET ROWCOUNT. This example explain everything :

SET ROWCOUNT @n
SELECT ID FROM TableName
SET ROWCOUNT 0

Then the second is with dynamic SQL :

use Northwind
declare @a int, @str varchar(100)
set @a = 10set @str = ’select top ‘ + cast(@a as varchar(100)) + ‘ * from Orders’exec (@str)

A little more complex, but worth to take a look at it.

Oh and also, on the new SQL Server that will be coming soon, this problem is solved and you can pass parameter to the TOP operator.