patternsqlMinor
switch TOP clause between percentage and rows by parameter
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.
other idea is to make dynamic TSQL query something like this
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.
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
endother 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 @commandIs 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
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).
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.