Implementation of select Limit
Vitor Pinho
vpp at sageinfologia.com
Fri Nov 9 05:55:42 EST 2001
Hello all,
We are trying to implement a stored procedure which mimics the LIMIT
clause of the MySql select implementation in SqlServer.
When we use only one stored procedure our problems is related with column
names returned for each row. We cannot get all the results, PHP
mssql_query dont return any information and mssql_fetch_row also.
Our procedure is this:
========================================================================
CREATE PROCEDURE sp_limit @sql nvarchar(1000), @min int, @num int AS
SET NOCOUNT ON
DECLARE @sqlcur nvarchar(100)
DECLARE @i int
SET @sqlcur = 'DECLARE limit_cursor INSENSITIVE SCROLL CURSOR FOR ' +
@sql
exec(@sqlcur)
OPEN limit_cursor
-- Fetch the nth row in the cursor.
FETCH ABSOLUTE @min FROM limit_cursor
SET @i = 1
WHILE (@i < @num AND @@FETCH_STATUS = 0)
BEGIN
-- Fetch the row immediately after the current row in the cursor.
FETCH NEXT FROM limit_cursor
SET @i = @i +1
END
CLOSE limit_cursor
DEALLOCATE limit_cursor
SET NOCOUNT OFF
GO
========================================================================
Question 2:
If we try to implement the above stored procedure in different steps in
PHP, we are able to declare and open cursor. However the FETCH NEXT
doesnt return any results (we using PHP with mysql_query again).
Has anyone done this implementation of LIMIT? Does anyone sees other
solutions for this problem?
Were using PHP accessing MSSQL server using freetds 0.52.
Best Regards,
Vpp
More information about the FreeTDS
mailing list