How to return a set number of rows in SQL using a variable

You're gonna love me for this one :)

I'll make it very simple too.

So I am sure you've use the TOP clause to retreive a certain number of rows from a query, right?


Here is an example of how you can set the value of TOP as a variable based on, let's say...user input.

CREATE PROCEDURE [dbo].[testRowCount]
    (
        @total int=0  input variable. This can be based on anything like user input, etc.
    )

AS
DECLARE @top INT what you will use as the TOP variable
SET @top = @total  here you set the value for TOP based on the input

And here is how you use in in the select statement

SELECT TOP(@top) *
            FROM  table
WHERE....



You're welcome :)