offset and limit

[Anon]

In mysql you can specify the startposition of the result as the offset and limit as the number of rows to be returned. For example;

SELECT * FROM TABLE LIMIT $offset, $limit

How do I do it in MSSQL?

Thanks / Henrik

[Anon]

I was looking to do the same thing.

what is the equivilant Microsoft sql statement to MySQL's LIMIT??

[Anon]

For a long time 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!
@ 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

Please note that this will return separated results so you will need to use the mssql_next_result function to use all the result set.

[Anon]

Has anyone successfully implemented this Stored Procedure?

[Anon]

I use this code on several production systems. It is a great way to add pagescrolling into PHP/MSSQL applications. Here is my method for getting a dataset from the SP:

===============================

function DoLimitQuery( $query, $offset, $limit = 0 ) {
// make sure to escape double quotes in the query
$query = str_replace( '"', '""', $query );

$this->DoQuery( "LimitSelect \"$query\", $limit, $offset" );

$rows = array();
do {
	$rows = array_merge( $rows, $this->GetRecords() );
} while ( mssql_next_result( $this->Query_ID ) );
return $rows;

}

===============================

and here is the code for the SP:

===============================

*
Created by Frederick D. Whitlark, 08/06/2001

LimitSelect is a Microsoft SQL Server T-SQL stored procedure 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 VARCHAR (1024), -- 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 @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

[Anon]

Thanks for the response.

I too am frustrated this functionality is not supported in MSSQL.

I have done this successfully in PHP/MySQL and now I want to do the same with PHP/MSSQL.

The example function you provided has mismatched variables.

Can you please provide a cleaner version?

[Anon]

Thank YOU! I successfully implemented use of the SP.

However, I was slightly confused with the SP stating

Syntax:
LimitSelect query, offset, limit

and

function DoLimitQuery( $query, $offset, $limit = 0 ) {

Uses:
$this->DoQuery( "LimitSelect \"$query\", $limit, $offset" );

and it works!

[Anon]

<?do {
while ( $row = mssql_fetch_array ( $resultsearch) ) {

	#THIS DOESN'T WORK -  WHY?

		$result = $db->query("SELECT * from DropDowns where Label = 'SysPrimeComp'");
		while ( $dropdown_row = mssql_fetch_array ( $result ) ) {                    
                  <?print $dropdown_row[Entry]?>
         } # end while 

	}# end while 
} while ( mssql_next_result( $resultsearch ) );?>

Why can't I query within the LimitSelect?

[Anon]

In the example:
<?print $dropdown_row[Entry]?>

the php tags shouldn't be there.

I'm not having a syntax problem, just a problem querying within the LimitSelect.

thanks!

[Anon]

How do I make a link "Next Page" or "Previous Page" to the next offset & limit. e.g Page1, Page2... Just like in a guestbook?

[viliak]

there was a mistake in previous procedure

@query CHAR (256),
@offset INT,
@ INT
---------------------
use this...

CREATE PROCEDURE [owner].[LimitSelect]
@query CHAR (256), -- SQL query, it'd better be a SELECT!
@offset INT, -- start result set from offset
@ INT -- limit the result set of the query
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

-- 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

[Anon]

Your SP is great. However, I am new to PHP and am using ODBC. Is there an equivalent for mssql_next_result() for odbc ? If not, is there a work around ?

Thanks,

JT

[Anon]

I think you can do it with the odbc_next_result function listed here:

http://www.php.net/manual/en/function.odbc-next-result.php