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.

• prajey

  SSC Enthusiast

  Points: 172

  More actions

  October 19, 2010 at 5:06 am

  #1238941

  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: 323522

  More actions

  October 19, 2010 at 6:37 am

  #1238995

  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

  October 19, 2010 at 11:45 am

  #1239243

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