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