How to return a range of records in procduces?

[sunlxy]

I want to write a procduce to get a range of records, such as the 50th to 100th,how can I do it? Use CURSOR? Can you give me some examples?

 

[Andel]

Basically, a simple example is this:
SELECT * FROM tablename
WHERE field >= 50 AND field <= 100

But if you're retrieving a range of data like 50th, 241st, 332nd, etc... you have to eliminate the 2 characters after the number first (th, nd, st, etc...). See example below.

SELECT * FROM table
WHERE LEFT(field, (LEN(field) - 2)) >= 50 AND
left(field, (LEN(field) - 2)) <= 100

Hope this helps....

Andel

 

[mfairchild]

I believe you are asking &quot;How do I return the 50th through 100th record of a result set?&quot; Potentially for paging reasons, etc. Kind of like using the &quot;TOP&quot; keyword in a SELECT statement, but you want some out of the middle instead of from the top...

You could use a cursor, but you could also use a temp table that has an identity column defined. Of course, if you have a ton of data this may be bad, but then a cursor would be bad too.

This is what I've done. I'm not sure I like it, but it worked.

Create Procedure NewStoredProcedureName
@Min_Record integer,
@Max_Record integer
AS

CREATE TABLE #Temp
(Record_Number numeric IDENTITY,
Field1 integer,
Field2 datetime,
Field3 varchar(5))

INSERT INTO #Temp (Field1, Field2, Field3)
SELECT t.Field1, t.Field2, t.Field3
FROM Table t
ORDER BY t.Field1

SELECT Field1, Field2, Field3
FROM #Temp
WHERE (Record_Number >= @Min_Record
and Record_Number <= @Max_Record)
ORDER BY Record_Number


If you want to get record 50 to 100 and then get record 100 to 150, the Order by clause in the insert/select is extremely important so the identity field value is assigned to the same record each time.

Hope this helps.

 

[sunlxy]

Thanks for your answers.I still have problem about it.
If I have a table which contains 100,000,000 records,but I only want to get 20 records once,such as from 100,500th to 100,520th.Use temp table,I must first select at least 100,520 records, it's terrible,right?
Can you point out what different between temp table and cursor? What method should I use? Run fast is what I care.

 

[mfairchild]

I'd rather select 100,000,000 records into a temp table then declare a cursor, loop through each one, setting variables along the way. In my experience, if you can get away from using cursors, do it. SQL Server isn't very efficient at looping. Then again, if you have 100,000,000 records, you may want to think about doing something so you don't have to do that sort of thing at run-time. If the data is static, you could put the identity field on the table itself and write something to insure that the field is kept sequential.

 

[Andel]

Hi, have you tried this:

SELECT TOP 20 * FROM table
WHERE unique_field NOT IN
(SELECT TOP 100500 unique_field FROM table)



Andel
maingel@hotmail.com