Hi all,
Does anybody know of an easy way to select the top N records of a table, where the select uses an order by clause, and N is dynamic (for example, is passed as a parameter to the stored procedure)? The "SELECT TOP" syntax must be hardcoded, and "SET ROWCOUNT" unfortunately limits the number of rows before the rows are sorted in an "order by". I have been doing it with a while loop, using "SELECT TOP 1" each time, but I'd like to employ a more elegant and less complex solution, if one exists. I could construct the entire query dynamically use "sp_executesql", but I wonder if this would prevent SQL Server from caching the execution plan.
Does anybody know of an easy way to select the top N records of a table, where the select uses an order by clause, and N is dynamic (for example, is passed as a parameter to the stored procedure)? The "SELECT TOP" syntax must be hardcoded, and "SET ROWCOUNT" unfortunately limits the number of rows before the rows are sorted in an "order by". I have been doing it with a while loop, using "SELECT TOP 1" each time, but I'd like to employ a more elegant and less complex solution, if one exists. I could construct the entire query dynamically use "sp_executesql", but I wonder if this would prevent SQL Server from caching the execution plan.
