June 9, 2002 at 12:40 pm
I currently need to be able to select the top N records based on a parameter that is passed in from the application level. What I currently have is:
SET @sql = 'SELECT TOP ' + CONVERT(varchar(10), @number) + ' rest of query'
exec (@SQL)
Is there a different/better way to do this?
June 9, 2002 at 1:32 pm
Look at SET ROWCOUNT which will offer the ability to do this in a SP much better.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 9, 2002 at 2:28 pm
Thanks for the quick reply, but I was totally unclear as to what I really needed. If you look at the code below I am retrieving this data via paging and need both of the TOP clauses to be dynamic. The only way I have got this to work is using EXEC (@myquery). I am also curious if using sp_executesql would help in this instance or if the dynamic TOP would cause SQL not to reuse the query plan.
select top 10 ArcadeTournament.atrn_GUID, ply_FirstName, ply_LastName, agam_NumberOfPitches, atrn_StartDate, tm_HomeUID, tm_AwayUID, gm_Date
from ArcadeTournament
join ArcadeResults on ArcadeTournament.atrn_UID = ArcadeResults.atrn_UID
join ArcadeGame on ArcadeTournament.atrn_UID = ArcadeGame.atrn_UID
join BSB_Player on ArcadeGame.ply_PitcherUID = BSB_Player.ply_UID
join BSB_Game on ArcadeGame.gm_UID = BSB_Game.gm_UID
where ArcadeResults.husr_UID = @user-id
and ArcadeTournament.atrn_UID not in
(selecttop 10 ArcadeTournament.atrn_UID
from ArcadeTournament
join ArcadeResults on ArcadeTournament.atrn_UID = ArcadeResults.atrn_UID
where husr_CreatorUID = @user-id
order by atrn_StartDate desc
)
order byatrn_StartDate desc
Thanks for the assistance.
--joe
June 9, 2002 at 6:15 pm
Check out this thread, another poster had a similar piece. http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4082&FORUM_ID=23&CAT_ID=2&Topic_Title=Parameter%20to%20the%20TOP%20operator&Forum_Title=General
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply