I really appreciate if you can share the link with your friends via Facebook,twitter,blog etc.

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