Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2008-07-08   |   Comments (5)   |   Related Tips: More > 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


Last Updated: 2008-07-08


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, February 08, 2013 - 7:56:58 AM - Richard Back To Top

Thanks, very useful


Saturday, August 08, 2009 - 2:46:40 PM - dbbishop Back To Top

And actually, the second statement should be:

SELECT TOP(1) @top = SortOrder FROM PhoneType


Saturday, August 08, 2009 - 2:43:07 PM - dbbishop Back To Top

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

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

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


Learn more about SQL Server tools