Nov
10

Tips & Tricks: How to use a variable in a TOP clause of T-SQL

MDBitz - Matthew Denton T-SQL, Tips & Tricks Add you comment

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.

, ,