HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

switch TOP clause between percentage and rows by parameter

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
rowstopparameterbetweenandswitchclausepercentage

Problem

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 stored procedure based on passed param.

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


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 ' '
                     end
                   + ' * '
                   + '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.

Solution

To elaborate on gbn's answer, two stored procedures 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 stored procedures 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).

Context

StackExchange Database Administrators Q#7339, answer score: 4

Revisions (0)

No revisions yet.