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.
Comments & Questions
Add Your Comment