Sebastien Lachance

Learning new things everyday

Select a number of rows based on a parameter using T-SQL

Today I was trying to select a predetermined number of rows in a table using a parameter… Syntax Error.

SELECT TOP @n ID FROM TableName

After a small research I found two solutions to this problem. First, the SET ROWCOUNT :

SET ROWCOUNT @n

SELECT ID FROM TableName

SET ROWCOUNT 0

 

The second solution is with dynamic SQL :

declare @a int, @str varchar(100)

set @a = 10

set @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.

Comments