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

avatar image

Katie 1
1.4k 132 164 205

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

Insert 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

avatar image

Fatherjack ♦♦
43.7k 79 97 117

(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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

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:

x2072
x1066
x424

asked: Apr 18, 2010 at 05:13 PM

Seen: 2326 times

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

Copyright 2016 Redgate Software. Privacy Policy