note 14622 deleted from function.mssql-fetch-array by jimw

I've been frustrated by the lack of limit and offset support offered by SQL Server. What I wanted was something that worked just like MySQL's LIMIT parameter for returning a portion of a query result set. I needed this functionality for spanning a result set across multiple pages. At first, I did this in PHP code after returning the entire contents of a table. This works great until your table gets big. What I really needed was to add Limit and Offset functionality into SQL Server as a stored procedure. SQL Server's SELECT TOP ('SELECT TOP i * FROM resource') works fine for returning a limited result set but it does not offer an offset option. To achieve a Limit and Offset that behaves like MySQL's hand LIMIT O, L parameters you will need to write a T-SQL stored procedure using a cursor. Here is the necessary code for the SP: --------------------- /* 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 (128), -- SQL query, it'd better be a SELECT! @limit 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 @limit = @limit - 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 < @limit) 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 --------------------- Please note that this will return separated results so you will need to use the mssql_next_result function to use the result set.