Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Dynamic TOP
From Wiki
How can you use a variable with top? First create this table
- CREATE TABLE #Test (id INT)
- INSERT #Test VALUES (1)
- INSERT #Test VALUES (2)
- INSERT #Test VALUES (3)
- INSERT #Test VALUES (4)
- INSERT #Test VALUES (5)
- INSERT #Test VALUES (6)
Yoo want to run this but you would like the user to supply a parameter instead of 3
- SELECT TOP 3 *
- FROM #Test
- ORDER BY ID
In SQL Server 2005 you can use TOP with a variable. SELECT TOP (@var) FROM.... The variable has to be enclosed in parentheses, they are required Example
- DECLARE @TOP INT
- SELECT @TOP = 3
- SELECT TOP (@TOP) * FROM #Test
- ORDER BY ID
For the SQl Server 2000 version we can use SET ROWCOUNT (this has been deprecated in SQL Server 2005)
Example
- DECLARE @TOP INT
- SELECT @TOP = 3
- SET ROWCOUNT @TOP
- SELECT * FROM #Test
- ORDER BY ID
- SET ROWCOUNT 0
Make sure to reset ROWCOUNT to 0 after the select since it might affect any other DML operations you have below this code
Contributed by: --SQLDenis 02:11, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Sorting, Limiting
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.