In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at the TOP operator and find out what has been changed in SQL Server 2005 to make it much better compared to SQL Server 2000. In SQL Server 2000 there are 2 ways you can have a query use a variable to limit the number of rows returned. You can use dynamic SQL or you can use ROWCOUNT

Let’s take a look at those methods, here is our regular TOP query which limits the rows to 30

SELECT TOP 30 * 
FROM sysobjects
ORDER BY name

If we just change 30 to a variable, we will get an error

DECLARE @t INT
SELECT @t = 30

SELECT TOP @t * 
FROM sysobjects
ORDER BY name

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘@t’.

One way we can accomplish our goal is by using SET ROWCOUNT, run the query below and you will see that it returns the same results as our first query

DECLARE @t INT
SELECT @t = 30

SET ROWCOUNT @t

SELECT  * 
FROM sysobjects
ORDER BY name

SET ROWCOUNT 0

Make sure that you set ROWCOUNT back to 0 otherwise all your queries will be limited to 30. You don’t have this problem when using TOP. Also using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server, so if you are using it with those DML statements rewrite to use TOP

Dynamic SQL is another possibility to do a dynamic TOP. Below is what the query looks like, as you can see it is pretty messy.

DECLARE @t int 
DECLARE @SQL varchar(8000)
SELECT @t = 30

SELECT @SQL = 'SELECT TOP ' + convert(varchar(20),@t) + 
			' * FROM sysobjects ORDER BY name'

EXEC (@SQL)

Dynamic TOP in SQL Server 2005 and up

Okay, you moved all your code to a 2005+ instance and now you are trying to execute the query that used a variable with TOP again

DECLARE @t INT
SELECT @t = 30

SELECT TOP @t * 
FROM sysobjects
ORDER BY name

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘@t’.

Okay, you got the same helpful message as before….what can you do?

In order to use TOP with a variable, you need to enclose the variable in parentheses

Here is how

DECLARE @t INT
SELECT @t = 30


SELECT TOP (@t) * 
FROM sysobjects
ORDER BY name

It is as simple as that, it is really trivial now to return a result set where the user can pass in a value for how many results he wants back

Come back tomorrow for post number 10 in this series