Implementing the user input and rownumber in sql server from Oracle

Question

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!

Answer 1

0

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
)
GO

Insert into RowSelectorReference 
select 1, 0, 2000000
union
select 2, 2000001, 4000000
union
select 3, 4000001, 6000000

answered Apr 18 '10 at 06:46 PM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

(comments are locked)

Answer 2

0

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

answered Apr 18 '10 at 07:20 PM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

ah, ooh, oh. Rownumbers needed. That rules out my suggestion then. well spotted Kev

Apr 18 '10 at 07:45 PM Fatherjack ♦♦

obviously not.......

Apr 19 '10 at 05:01 AM Kev Riley ♦♦

(comments are locked)

Implementing the user input and rownumber in sql server from Oracle

Follow this question

Related Questions