Dynamically controlling the number of rows affected by a SQL Server query
July 9th 2008 04:14
Dynamically controlling the number of rows affected by a SQL Server query
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(@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( ).
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(@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( ).
| 61 |
| Vote |
Add To: 
