:::: MENU ::::

Using parameter for rows with TOP in SELECT statement

  • Oct 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Using parameter for rows with TOP in SELECT statement

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:

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).

Consult us to explore the Databases. Contact us