FreeTDS Development Group

["Vitor Pinho" <vpp AT sageinfologia.com>]

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 don’t 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
doesn’t 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?

We’re using PHP accessing MSSQL server using freetds 0.52.

Best Regards,
Vpp