T-SQL allows us to retrieve a specific number of rows by using TOP operator in SELECT statement. Following statement would retrieve top 10 rows containing name and number from PRODUCT table.
SELECT TOP 10 Name, ProductNumber FROM [AdventureWorks].[Production].[Product] GO
Of course you may filter the rows by using WHERE clause and other ways as used in normal select statement. It is fine till here, until you are required to change the provided number (10 in above statement) with some dynamic parameter with TOP clause. Trying following code would generate error
DECLARE @rows int SET @rows = 13 SELECT TOP @rows Name, ProductNumber FROM Production.Product GO
Solution to this problem is simple. Just enclose the parameter in SELECT statement with in a bracket. So our working code would be some thing like
DECLARE @rows int SET @rows = 13 SELECT TOP(@rows) Name, ProductNumber FROM Production.Product GO
You may use this syntax in functions, procedures or scripts when required to use Using parameter for rows with TOP in SELECT statement. I have test this syntax on SQL Server 2005 and SQL Server 2008 instances and it worked well. May be it would not work on SQL Server 2000 (i read but not tested).