Paging in MSSQL

[amgreg]

Ok, I've searched the forum and I've found the information regarding MySQL databases, but I can't find the information regarding MS SQL databases. Does any one have the vaguest idea of how to paginate using MS SQL? Please?

[netbros]

No probs...

As I'm sure you know, MS-SQL doesn't have a LIMIT clause like MySQL does... so here's how you get around it.

1. Call your SQL statement as normal

2. Assuming you want to start reading results from record #10, call @mssql_data_seek ($Query, 10) where $Query is the return value from mssql_query () and 10 is the record # to start from. The @ is important in front of the function name as it prevents an error message if there aren't 10 records in the result set.

3. Keep reading the results through mssql_fetch_array () or whatever you use, and use a counter to stop reading when you've read the number of records you want to display on the page.

Simple, eh? 😃

[spannah]

Netbros:

Although your solution works, it could be very ineffecient.

Lets say you only want to display 10 records but your query returns 10000. You just transfered 9990 records for no reason.
PLease let me know if I am worng on this.

The proper way to do it should be at the query/database level, so only 10 records are returned.

I am still looking for the proper way to do this...