Dynamically controlling the number of rows affected by a SQL Server query

By:   |   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.Employee

The 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.Employee

This 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(@topHumanResources.Employee SET MaritalStatus 'S' WHERE EmployeeID 20


-- delete example
DECLARE @top INT
SET 
@top 10
DELETE TOP(10HumanResources.Employee WHERE EmployeeID 20


-- insert example
DECLARE @top INT
SET 
@top 10
INSERT TOP(@topdbo.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
  • Lastly, if you use SET ROWCOUNT make sure you reset it to return all rows using SET ROWCOUNT 0


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux 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



Comments For This Article




Friday, February 8, 2013 - 7:56:58 AM - Richard Back To Top (22008)

Thanks, very useful


Saturday, August 8, 2009 - 2:46:40 PM - dbbishop Back To Top (3848)

And actually, the second statement should be:

SELECT TOP(1) @top = SortOrder FROM PhoneType


Saturday, August 8, 2009 - 2:43:07 PM - dbbishop Back To Top (3847)

Either, but if you use the first, the SELECT statment must be enclosed in parenthises:

SET @top = ( SELECT TOP(1) SortOrder FROM PhoneType )


Saturday, January 24, 2009 - 3:03:12 PM - weitzhandler Back To Top (2603)

So far so good, but how do I do this:

DECLARE @top int

SET @top = SELECT TOP(1) SortOrder FROM PhoneType

-- OR --

SELECT @top = TOP(1) SortOrder FROM PhoneType


Monday, July 21, 2008 - 9:39:25 AM - acukier Back To Top (1474)

Excelent tip.  Very useful and easy to implemenet.  To be remembered















get free sql tips
agree to terms