Implementation of select Limit

Brian Bruns camber at ais.org
Fri Nov 9 07:32:06 EST 2001



Doesn't SQL Server have a limit-type statement? Sybase doesn't but I'm 
pretty sure MS added one....back, back, back into the recesses of my mind, 
I think its like:

select top 100 * from table

or something like that.

I'll take a look at this this weekend anyway.

Brian

On Fri, 9 Nov 2001, Vitor Pinho wrote:

> 
> 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
> 
> 
> ---
> You are currently subscribed to freetds as: [camber at ais.org]
> To unsubscribe, forward this message to $subst('Email.Unsub')
> 




More information about the FreeTDS mailing list