Dynamic SQL or How do I SELECT TOP @var records?

By Bill Graziano on 1 August 2000 | Tags: Queries

Ben writes "I'm trying to create a stored procedure where I can send "sp_GetTopRecordSet 25" and it will return a recordset of the top 25 records of my query, like: "SELECT TOP @n * FROM MyTable ORDER BY DateColumn" Now, why won't this work?"

Well Ben, you've discovered a weird quirk of SQL Server: it's very picky about where it allows variables in queries. Another question posted after yours asked why you couldn't put a whole WHERE clause in a variable (Select * from table where = @whereclause). The SQL Server parser just won't let you. I'll cover both these questions here.

The easiest answer is to use the SET ROWCOUNT statement. This statement stops processing after a certain number of rows have been processed. It works for SELECT, UPDATE and INSERT. In your case the syntax would look something like this:

declare @v1 int
set @v1 = 25
set rowcount @v1
select * from MyTable Order by DateColumn
set rowcount 0

Always remember to use SET ROWCOUNT 0 to turn off the row limiter. You can the SQL Server Books Online for further details on this command. There really isn't much more to it though. Micrsoft suggests using the TOP command whenever possible.

So how would you use the TOP in this case? Glad you asked. Easy, just make the whole SQL statement a variable. In this case, your query is:

declare @vSQL varchar(1000), @numrows int
select @numrows = 25
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' * from MyTable Order by DateColumn'
Execute (@vSQL)

The EXECUTE statement will run any valid SQL statement that you pass it. You can use this to dynamically generate SQL statements at run time. Keep in mind that SQL Server is providing no syntax checks of this statement until it actually runs so be very careful.

You can also use this approach to solve the problem from above with the dynamic WHERE clause. Just put your whole query into the a variable. You can build the query as you go based on the user input.