December 24, 2011 at 3:48 pm
Hi,
I am trying to write a generic stored procedure in sql 2008r2 for top 10 sales.I need to be able to display top 10 sales by colour or any other option.This procedure could then be reused for any other type of report ie top 10 colours etc
I could create a new stored procedure for each type of query but would prefer to have one stored procedure which could handle top 10.
My query currently could look like the following
select top 10 * from table1
group by Colour
I think i need to know can i pass a parameter to the group by clause and is this possible? or what is the best way to implement this.
Thank You
J
December 25, 2011 at 2:51 am
No, you can't do that. What you can do however, is using dynamic SQL. Some DBAs are very much against dynamic SQL, others use it all the time. What I can say for sure, it truly can reduce development time, and allow the code to run pretty much otpimally for any combination of parameters, but needs thorought testing. So, for instance:
declare @sql nvarchar(max);
set @sql = 'select ';
if @param = color
set @sql = sql + ' color, '
-- more if statements here
set @sql = @sql + ' from table group by ';
if @param = color
set @sql = sql + ' color';
exec sp_executesql @sql;
I think you get the idea.
December 25, 2011 at 7:11 pm
When using dynamic sql, and depending upon how it is entered (from a web application / interface only available to a trusted few employees), you should be aware of the ever present danger of a injection attack.
So before composing your code read this article, and search this site for additional articles on the same subject
http://www.sqlservercentral.com/articles/Security/sqlinjection/1269/
December 25, 2011 at 11:16 pm
Hi,
This sample may be helpfull.
declare @Rel char(100);
SET @Rel='surname'
declare @sql nvarchar(2000);
set @sql='Select Top 10 ';
SET @sql=@sql+' CASE WHEN @Rel=''surname'' then surname ELSE given_names end'
set @sql=@sql+' AS '+@Rel
set @sql=@Sql+' from person
group by CASE WHEN @Rel=''surname'' then surname ELSE given_names end'
exec sp_executesql @sql, N'@Rel char(100)',@Rel
Malleswarareddy
I.T.Analyst
MCITP(70-451)
December 26, 2011 at 4:07 am
GROUP BY won't give you what you're looking for - it's the aggregate operator. ORDER BY gives you TOP. However, unless you have a suitably-constructed rollup table or view, you will almost certainly require different levels of aggregation for different entities.
Write the queries out for the different entities such as colour that you might want to run this for, and compare them. If there are significant differences between them, figure out a common aggregation level for each and run the results into a temp table. This gives you a common point from which to perform your final SELECT.
There are a number of tricks that you can use to choose between different sorts at run time, including dynamic SQL.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply