You may find that when developing Select queries for an Application that you may want to dynamically limit the results returned from a Stored Procedure. Upon first inspection you may think oh lets use a TOP clause with a limit variable passed in when the stored procedure is called.
CREATE PROCEDURE getROWS @limit INT AS BEGIN SELECT TOP @limit * FROM table ORDER BY modTS DESC END GO
Unfortunately Sybase T-SQL does not allow variables in the limit clause. Don’t worry though the ROWCOUNT variable can be paramaterized. This gives us the following procedure that limits the results returned to our desired amount.
CREATE PROCEDURE getROWS @limit INT AS BEGIN SET ROWCOUNT @limit SELECT * FROM table ORDER BY modTS DESC SET ROWCOUNT 0 END GO
That is all there is to it, simply remember to use a ROWCOUNT statement instead of a TOP clause in your stored procedures.