November 15, 2001 at 5:13 am
I want to be able to select the top n results from a results table in a stored procedure but depending on the event I want to vary how many 'n' is.
I thought that this would do the trick:
CREATE PROCEDURE sp_best ( @NumBest [int], @Entry [int])
AS select top @NumBest Results.RunNo, Results.Points FROM Results where Results.EntryID = @Entry order by Results.Points DESC
The idea being that i could pass in NumBest as the parameter for the TOP N clause. It doesn't work!
Surely there is a smart way to do this that doesn't involve dynamically creating the sql text?
Thanks for any help
Matt Stephens
November 15, 2001 at 5:18 am
Seems like there should be, but it doesn't spring to mind. Building the string and using Exec() would certainly work. Another solution, not pretty, would be to do something like this:
if NbrRows=1
Select top 1
elseif NbrRows=2
Select Top 2
etc!
Andy
November 15, 2001 at 8:32 am
use
set rowcount @NumBest
and skip top
November 15, 2001 at 8:44 am
Why do you not want to use dynamic SQL? If you use sp_executesql and write it correctly your execution plans will be reused. If you use SET ROWCOUNT the optimizer might get confused, but it will recognise TOP.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
November 15, 2001 at 12:19 pm
The query plan will only be reused if it is in memory. These have a much lower priority than stored procedures and get phaased out relatively quickly.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply