x

Implementing the user input and rownumber in sql server from Oracle

Hello all,

I am actually working on the migration of some of the stored procedures from Oracle to sqlserver 2008, and came across one stored procedure, which is actually used to work with one of the datastage jobs. The requirement of the stored procedure:>
is There are 22 million rows in a table and stored procedure is intended to extract the data based on the user input
for example, if the user input given is 1, the stored procedure is needs to get the first 2 million records out of the 22 million records,if the user input given 2 then the next set 2 million records(i.e from 2 mill to 4 million) are required to be fetched based on the row number and so on untill it reaches 22 million> here is the sample query.

select * 
   from ( select /*+ FIRST_ROWS(n) */a.*, ROWNUM rnum 
   from ( select * from t   order by id) 
   where ROWNUM <= :MAX_ROW_TO_FETCH 
   where rnum  >= :MIN_ROW_TO)_FETCH;

Any help is appreciated!

more ▼

asked Apr 18, 2010 at 05:13 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

can you not use a lookup table to give the user provided value with an upper and lower limit?

Create table RowSelectorReference ( UserParam int not null,, LowrLimit int not null UpprLimit int not null ) GOInsert into RowSelectorReference select 1, 0, 2000000 union select 2, 2000001, 4000000 union select 3, 4000001, 6000000 
more ▼

answered Apr 18, 2010 at 06:46 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left

use a cte with row_number function

;with cte (rownum,col1, col2,...coln) as  (  select row_number()over (order by id) as rownum, col1, col2, ..., coln  from t  )select col1, col2,...coln from cte where rownum between MIN_ROW_TO_FETCH and MAX_ROW_TO_FETCH 
more ▼

answered Apr 18, 2010 at 07:20 PM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

ah, ooh, oh. Rownumbers needed. That rules out my suggestion then. well spotted Kev
Apr 18, 2010 at 07:45 PM Fatherjack ♦♦
obviously not.......
Apr 19, 2010 at 05:01 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1853
x991
x379

asked: Apr 18, 2010 at 05:13 PM

Seen: 2143 times

Last Updated: Apr 18, 2010 at 06:29 PM