SELECT TOP n

rgerald · May 12th, 2006, 01:44 PM

Does anyone know a way to parameterize a SELECT TOP n .... query so that the n can be passed into the statement as a variable?

I would like to be able to do something like this:

Code:

DECLARE @TopCount integer
SET @TopCount = 100

SELECT TOP @TopCount * FROM MyTable ORDER BY RecordDate

Rand

SqlMenace · May 12th, 2006, 01:51 PM

That will work in SQL server 2005 but not in 2000
you can use SET ROWCOUNT in 2000

DECLARE @TopCount integer
SET ROWCOUNT 100

SELECT * FROM MyTable ORDER BY RecordDate

SET ROWCOUNT 0 --make sure you do this at the end

------------------------------------------ http://sqlservercode.blogspot.com/

rgerald · May 12th, 2006, 02:14 PM

Thank you SQLMenace for the advice about ROWCOUNT.

It works, but unfortunately it also messes up any subqueries. I guess I'll just have to wait for SQL Server 2005 to be installed here.

Code:

DECLARE @TopCount integer
SET @TopCount = 100

SET ROWCOUNT @TopCount

SELECT * FROM MyTable ORDER BY RecordDate

SET ROWCOUNT 0

Rand

joefawcett · May 12th, 2006, 04:03 PM

Your alternative is to construct the SQL dynamically and execute using exec or sp_executeSql.

--

Joe (Microsoft MVP - XML)