06 June 2011

Dynamic Top Clause in sql

All of us know about the TOP clause in sql, it returns the top 'n' records from a result set.
But you ever thought about dynamic TOP .
What i mean is,we want to fetch Top 'n' records from a result set where n will be provided as  a stored procedure parameter or some other manner.
Query:-
Select top @NumberOfRecords From TblFriends
--@NumberOfRecords  is a sql variable
This query will throw an error ' Incorrect syntax near top'.
Solution:-
Select top (@NumberOfRecords) From TblFriends
--here, we can set @NumberOfRecords to any integer value,even to some aggregate function
--like, select @NumberOfRecords=min(age) from TblEmployee

Hope you enjoyed it, will back with some more interesting stuffs.
(please put comments if you like it.)