Returning a Dynamically Specified Number of Rows From a Stored Procedure

by rBellantoni 3/25/2009 1:15:00 PM

Have you ever wanted to return the top X number of rows from a query?  Of course you have!  This can be accomplished easily in T-SQL using the TOP option of the SELECT statement.

	SELECT TOP 10       EmployeeID,       FirstName,       LastNameFROM   EmployeeORDER BY       Utilization
	

But what if the number of rows you want to return is unknown?  Say, for example, this query is for a report and the end-user gets to specify how many results they want.  Unfortunately, T-SQL does not provide the ability to say

	SELECT TOP @n
	

What do you do?  Do you just return all of the results and let the report do the filtering?  This would result in wasted resources in both returning and then removing (or logic to ignore) the extraneous data.  Do you write dynamic SQL, instead, to get the correct number of rows?  Taking this approach would negate some of the benefits of implementing stored procedures in the first place (user security, injection attacts, etc.).

Fortunately, there is a way to do exactly what we want in our stored procedure without resorting to dynamic SQL or worrying about extra data using the SET ROWCOUNT option!

SET ROWCOUNT causes SQL Server to stop processing the query after the specified number of rows are returned (http://msdn.microsoft.com/en-us/library/ms188774.aspx).

	/* Set the number of rows to return (from stored procedure parameter) */SET ROWCOUNT @numberOfResults
	
	/* Execute our query */SELECT EmployeeID,       FirstName,       LastNameFROM   EmployeeORDER BY       Utilization
	
	/* Set ROWCOUNT back to 0 to return all rows by default */SET ROWCOUNT 0
	

Tags:

Bulk Insert with Line Feed Row Delimiter

by ebarcza 3/9/2009 12:56:00 PM

 

\n does not work for rows that are terminated with just a LF/AScii 10 char

use char(10) instead in dynamic SQL

Tags: , , ,

SQL SERVER

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About the author

Name of author Author name
Something about me and what I do.

E-mail me Send mail

Calendar

<<  September 2017  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar

Recent comments

Tags

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Sign in