By: Greg Robidoux | Comments (5) | Related: > Functions System
Problem
One thing you may need to do is dynamically return a set amount of rows based on user input. This could be for a search function, reports, dropdown lists or whatever. Instead of hard coding a set value you would like to pass in a variable that will then determine the number of rows to return. How can this be done with T-SQL?
Solution
As with most things there are several ways this can be done. In this tip we will take a look at a couple of ways that you can control the amount of records that are returned in the result set.
With SQL Server 2005 the simplest way to do this is by using the TOP command to return only the TOP x records from the query. The following example shows you how to return the first 20 rows from the HumanResources.Employee table.
SELECT TOP 20 * FROM HumanResources.EmployeeThe only drawback to this approach is that it returns a fixed amount of rows each time the query is run.
To make this dynamic we can set the TOP value as a parameter as follows:
DECLARE @top INT
SET @top = 10
SELECT TOP(@top) * FROM HumanResources.EmployeeThis will return the first 10 rows from the query. Since this is now a parameter we can make the value 10, 20, 50, etc...
Another way this can be done is by using the ROWCOUNT option as follows:
-- set value
DECLARE @top INT
SET @top = 10
SET ROWCOUNT @top
SELECT * FROM HumanResources.Employee
-- set value to return all rows
SET ROWCOUNT 0 With both of these methods the same result will be returned. The drawback to the second approach is that you need to set the value and then reset the value so you do not limit other result sets. With the TOP command it is set for each execution of the query and the value is not maintained.
You can also use the TOP option for DELETE, INSERT or UPDATE statements as follows:
-- update example
DECLARE @top INT
SET @top = 10
UPDATE TOP(@top) HumanResources.Employee SET MaritalStatus = 'S' WHERE EmployeeID < 20
-- delete example
DECLARE @top INT
SET @top = 10
DELETE TOP(10) HumanResources.Employee WHERE EmployeeID < 20
-- insert example
DECLARE @top INT
SET @top = 10
INSERT TOP(@top) dbo.contact2
SELECT * FROM dbo.contact Something to note is that with future versions of SQL Server the SET ROWCOUNT option will not work for DELETE, INSERT and UPDATE statements, so you should plan on using the TOP( ) method instead. In addition, according to SQL Server Books Online SELECT's will continue to work using either TOP( ) or SET ROWCOUNT, but it is preferable to always use TOP( ).
Next Steps
- Now that you know you can set the TOP value by using a parameter you can setup stored procedures where you pass in the TOP value and the result set will return the requested rows.
- Remember when using TOP with a parameter you have to put the parameter inside parenthesis
- These work
- SELECT TOP(@top)
- SELECT TOP(10)
- SELECT TOP 10
- This does not work
- SELECT TOP @top
- These work
- Lastly, if you use SET ROWCOUNT make sure you reset it to return all rows using SET ROWCOUNT 0
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
