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

By Bill Graziano on 1 August 2000 | 12 Comments | 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.

Discuss this article: 12 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Returning a week number for any given date and starting fiscal month (2 May 2007)

Other Recent Forum Posts

eat scrumptious caffeine using these guidelines (0 Replies)

eat scrumptious caffeine using these guidelines (0 Replies)

eat scrumptious caffeine using these guidelines (0 Replies)

?????? ??????????? ?????? 2017 (0 Replies)

WITH EXECUTE_AS not working for sproc. (9 Replies)

Data loss during sql cluster failover (3 Replies)

How to make Ajax Accordian Visible based on dropdo (2 Replies)

Corrupt SMALL transaction log backups in Log Shipp (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -