Returning Data results of query in batches based on passed variable

Question

Returning Data results of query in batches based on passed variable

davidfreye 43184

Old Hand

Points: 316
More actions
October 18, 2010 at 11:31 am

#230545

I have a request to return 100 rows of data per batch based on a variable passed to the my query. How do I do this?
Do I load my results to a temp table with an added id and make them use the id as their clause to pick the batch of data they need? This seems unneeded as I am sure there must be a better way to pass them the correct data. Any help would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

prajey SSC Enthusiast Points: 172 More actions · Answer 1

if the requirement was known, then your underlying table must have been designed in the way you say.[a numeric / identity column]. if not, the easiest way to go about is what you have mentioned.

Lowell SSC Guru Points: 323504 More actions · Answer 2

here's one way to do it, using row number to select the batches of numbers;

declare @NumRows int

declare @i int

SET @NumRows = 10 --return 10 rows at a time

SET @i = 3 --i want the 3rd resultset, 30-40

SELECT * FROM (

SELECT

row_number() OVER(ORDER BY name) AS RW,

sysobjects.*

FROM sysobjects ) myAlias

WHERE myAlias.RW BETWEEN (@NumRows * @i)

AND ((@NumRows * @i)+ @NumRows)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

davidfreye 43184 Old Hand Points: 316 More actions · Answer 3

Yeah the rownumber worked great! I am going to be using this for multiple uses now.