Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows.

I have two ideas

Calling two different sub sproc based on passed param.

if @param ='percent'
      exec sp_data_TOP_by_Percent
if @param ='perRow'
    exec sp_data_TOP_by_PerRow

other idea is to make dynamic TSQL query something like this

declare @command nchar(max)

select @command = 'select top(10) '
                   + case @param 
                              when 'percent' then percent 
                              else ' '
                   + ' * '
                   + 'from table 
                        order by 1';
exec sp_executesql @command

Is there third solution for something like this ?
What is better approach ? First one avoiding dynamic TSQL but is harder to maintain code in two places. I am using MSSQL2005 as databse.

share|improve this question
I'd certainly go with @gbn's route of two procedures. A "helper" procedure wrapping other calls feels wrong but its a perfectly acceptable approach. – Mark Storey-Smith Oct 27 '11 at 21:35
up vote 4 down vote accepted

I'd have 2 separate queries/stored procedures just to avoid dynamic SQL.

An IF statement in one stored proc would be enough too

share|improve this answer

To elaborate on gbn's answer, two sprocs is the better way to go here; additionally, I'd go one step further and place the query (sans top clause) in a table-valued function and then have the sprocs be nothing more than a shell which calls the TVF and applies the top clause appropriately (by number of rows or percent).

The only caveat here being that if you use a TVF, try and make sure that it's an inlined TVF, as it allows the query analyzer to use statistics (much like it would a view).

share|improve this answer
Thanks for this answer and welcome to Casper – Jack Douglas Oct 28 '11 at 22:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.