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.

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

Need Help in SQL query optimization (2d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (3d)

Suspect Parameter Sniffing? (3d)

See values of a proc when it is called from another proc? (4d)

Varchar() datatype and C# SqlDataReader truncates string (4d)

Find the extra rows (4d)

How to represent birth and death record in mysql db without duplicating data of an existing persons table existing persons table (5d)

EPR updates issues (5d)

- Advertisement -