Use variable with TOP in select statement in SQL Server without making it dynamic

Quick dive into USING TOP clause with variables in SQL Server 2005+

The SQL Server 2005 update brought a flexible feature allowing to parameterize the TOP clause. Here's how to take full advantage of this functionality:

Set the stage: Preparing the variable

Before making the most of your variable with TOP, declare it and set it as an integer:

DECLARE @maxRows INT;
SET @maxRows = 20;


I'd say leave no row behind, but... well, we don't really want every row.

Stepping up: Using the variable within TOP

Ensure to house the variable in parentheses while using TOP in your SELECT statement:

SELECT TOP (@maxRows) FirstName, LastName FROM Employees;


Finally, a way to weed out the employees we don't want to see.

Road less travelled: SET ROWCOUNT

For the hardcore fans of versions older than SQL Server 2005, SET ROWCOUNT serves as an alternative to manipulate the count of rows rendered:

SET ROWCOUNT @maxRows;
SELECT * FROM Employees;
SET ROWCOUNT 0;


Always reset ROWCOUNT to 0 post-usage, because nobody likes unwanted results resting in their queries, right?

Masterstroke: Enhanced efficiency and control with TOP clause

Possessing the power to use variables with the TOP clause translates to augmented efficiency. Dictate your row limit at runtime, embrace calculated values, and revel in an extra layer of control over your data retrieval.

Empower your queries: Using expressions with TOP

Incorporating expressions with TOP can add a new dimension of dynamism to your queries:

DECLARE @salesPercentile DECIMAL(3,2);
SET @salesPercentile = 0.10; -- Top 10%? Yes, please.

DECLARE @totalSales INT;
SELECT @totalSales = COUNT(*) FROM Sales;

DECLARE @topSales INT;
SET @topSales = @totalSales * @salesPercentile;

SELECT TOP (@topSales) * FROM Sales ORDER BY SaleAmount DESC;


This technique allows for an adaptable stream of query results, especially handy for reports and in-depth data analysis.

Avoid the common pitfall: Preventing potential bugs

Playing safe by resetting ROWCOUNT to 0 shields you from unintended limits in your later statements. Also, adequately scoped variables prevent nasty collisions:

BEGIN
    DECLARE @topN INT = 100;
    SELECT TOP (@topN) * FROM Products;
END


Keeping @topN strictly within the block reduces the risk of running against the use of the variable elsewhere.