Emulating LIMIT on MS-SQL... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Emulating LIMIT on MS-SQL...

Author

Topic

NewMedia42
Starting Member

35 Posts

Posted - 2003-01-14 : 23:25:18

I've unfortunately been burdened with the task of converting a program written to use MySQL over to MS-SQL... While MySQL is SQL compatible, one of their extensions to the language was the SELECT modifier LIMIT, which controls the rows returned to a query, so functionally the query like:

SELECT TOP 10 * FROM [Table]

is the same as

SELECT * FROM [Table] LIMIT 10

No problem there, but LIMIT has another way it can be used, where it specifies what record it *STARTS* at, so with the following query:

SELECT * FROM [Table] LIMIT 5,10

Ten rows are still returned, but starting at the 5th record, so it would start halfway through the returns from the previous example. The most obvious application is for searches, etc where you have a next button, and would do something like "LIMIT 0,10" for the first page, "LIMIT 10,10" for the second page, "LIMIT 20,10" for the third page and so on.

My question is, how on Earth do I perform the same sort of operation with MS-SQL? I would like to avoid doing the obvious and just looping through the first X records to get to my starting point, and the closer it is in implementation to LIMIT the better (the less code I need to change). Any help or insight would be greatly appreciated!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-15 : 00:12:06

http://www.sqlteam.com/item.asp?ItemID=566

You can also take a look at rowcount it can be used like top and can even be assigned a variable to be used when needs are dynamic.

Alternatively you could insert into values a temptable with an identity column and then use a between in your where clause.

Edited by - ValterBorges on 01/15/2003 00:20:35

NewMedia42
Starting Member

35 Posts

Posted - 2003-01-15 : 00:19:07

Thanks for that pointer but I was already aware of that approach - I actually thought this approach sounded more interesting:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=16027

What I was hoping for was any new insights or thoughts, especially about this particular approach since it seems a bit more clear than the ASC -> DESC method... Or perhaps some sort of more general purpose Store Procedure...

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-15 : 02:48:18

Think of it has having a deck of cards removing cards from the top, flipping those over and then taking the amount you want.

SELECT Top PageSize *
FROM
(
SELECT Top Page*PageSize *
FROM TableX
ORDER ASC
)
ORDER DESC

since top does not accept parameters you can either do this using sp_executesql and create a dynamic sql string to execute
or
use temp tables or table variables and row count and do it in two passes
or
use temp table or table variables and identity or tally table with a between range filter.

If you use the method you linked to you will have to use dynamic sql.

If the table data doesn't change very often you could keep have an integer id column or identity field which is sequential in steps of 1 and have the records in order then you can use the following where

WHERE id between ((page-1)*pagesize)+1 and (page*pagesize)

Edited by - ValterBorges on 01/15/2003 02:51:52

NewMedia42
Starting Member

35 Posts

Posted - 2003-01-15 : 14:35:55

For what it's worth, here's the sp I made to deal with the problem - nothing special, and just a more generalized version of some of the other ones out there... This one uses the Microsoft articles approach of using an ID field to get the starting point for each page, so the last ID needs to be tracked from query to query in order to continue from where it left off.

I made it as general purpose as possible, so I could use it with whichever table performing whatever query I wanted. The only assumption made is that each database has an ID field that is a unique identifier for a record (mine always do). Here's a quick example for those interested:

EXEC spGetRange @Database='[Users], @StartID=0, @MaxReturns=10

and it will return the first 10 users. If the 10th user was ID 50, then the next query would be:

EXEC spGetRange @Database='[Users], @StartID=50, @MaxReturns=10

You can optionally specify a Select modifier and/or Where modifier to get back more specific returns. If anyone has any suggestions or modifications for the following code, I'd love to hear them! Thanks again to everyone on the forum, this is definitely the place for the SQL hardcore... :)

CREATE PROCEDURE spGetRange
@Database varchar(256)='',
@StartID int =0,
@MaxReturns int = 0,
@Select varchar(256)='',
@Where varchar(256)=''
AS

DECLARE @SQL varchar(1000)

IF @Select=''
SET @SQL = 'SELECT *'
ELSE
SET @SQL = 'SELECT '+@Select

SET @SQL = @SQL + ' FROM '+@Database

IF @Where=''
SET @SQL = @SQL + ' WHERE (ID > '+CAST(@StartID AS varchar(40))+') '
ELSE
SET @SQL = @SQL + ' WHERE (ID > '+CAST(@StartID AS varchar(40))+' AND ('+@Where+')) '

SET @SQL = @SQL + ' ORDER BY ID ASC'

SET NOCOUNT ON
SET ROWCOUNT @MaxReturns

EXEC(@SQL)

SET ROWCOUNT 0
SET NOCOUNT OFF
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources