limit in mssql?

Anon

in mysql I can use:

"select * from guestbook order by msgID DESC limit $offset,$limit"

where $offset is a number on wish line to star on and $limit is a number of how many records to show

I would líke to do the same thing i mssql

is there anyone who has an idea how?

.patrik

Anon

There are several ways to do this as outlined in various articles at SQLTeam, 4guysfromrolla, and other sites. They all seem to focus around using the TOP syntax in a record set reversed subquery to accmplish this task. For me, that solution doesn't work so well because it is locked down to a particular table and is not very flexible. Upon looking into it further, I finally decided to create a stored procedure to act as a wrapper around a SELECT query that would use a cursor to return only the desired limit and offset. Here is the SP code:

/*
LimitSelect is a used for returning a query result set with a limit and an offset.

Syntax:
LimitSelect query, offset, limit

Example:
LimitSelect 'Select * From users', 10, 5
This call would return 5 rows starting from the 10th row

Notes:
For offsets, the first row of the set is zero.
*/

CREATE PROCEDURE LimitSelect
@query CHAR (2000), -- SQL query, it'd better be a SELECT!
@ INT, -- limit the result set of the query
@offset INT -- start result set from offset
AS
-- Execute call to declare a global cursor (node_cursor) for the query passed to the SP
EXEC ('DECLARE node_cursor CURSOR GLOBAL SCROLL READ_ONLY FOR ' + @query)

-- open the global cursor declared above
OPEN node_cursor

-- tweak the starting values of limit and offset for use in the loop
SET @offset = @offset + 1
SET @ = @ - 1

-- advanced the cursor to the offset in the result set
FETCH ABSOLUTE @offset FROM node_cursor

-- counter i
DECLARE @i INTEGER
SET @i = 0

-- loop until limit reached by counter i
WHILE (@i < @)
BEGIN
-- fetch the next row in the result set and advance counter i
FETCH NEXT FROM node_cursor
SET @i = @i + 1
END

-- clean finish
CLOSE node_cursor
DEALLOCATE node_cursor

I like this solution because you can just call this SP with any wacky SELECT statement at any time. It is very extensible. I've not seen any significant performance problems with this solution and I use the SP against some queries that hit some pretty large tables.

Don't let anyone tell you that you should perform the limit and offset in your application. You'll have to move over the entire table from the DB to the App before you can filter the result set.

-David