In this article, we build upon our stored procedure and will demonstrate how to implement SORT and TOTAL row functionality.
SORT
Our sales manager is happy with our previous work. Now he requests the ability to sort the results, ascending and descending. In the web page, we provide an input select box where he can select which column to sort on. We also provide an input select box where he can select ascending or descending. If he selects descending, we pass “desc”. If he selects ascending, we pass nothing as ascending is the SQL default. We concatenate the values and pass it to a new parameter @orderby. The possible values for @orderby are “region”, “region desc”, “empid”, “empid desc”, “sumSales”, and “sumSales desc”. We alter the stored procedure. We add the @orderby parameter and an ORDER BY with CASE and PATINDEX. I chose this somewhat lengthy statement, as it is standard SQL. The alternative is to use dynamic SQL. (For brevity I have deleted the “sumSales”code block).
<Our Reporting Stored Procedure>
ALTER PROCEDURE CubeSP
(
@method varchar(50)=’groupSales’
,@region varchar(50)=’all’
,@startdate datetime=’1900/1/1′
,@enddate datetime=’2100/1/1′
,@empid varchar(50)=’all’
,@group varchar(5000)=null
,@orderby varchar(5000)=’region,empid’
)
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select @region=case when @region=’all’ then ‘%’ else @region end
select @empid=case when @empid=’all’ then ‘%’ else @empid end
if @method=’groupSales’ begin
select region,empid,sumSales=sum(N)
from
(
select
region=case when patindex(‘%region%’,@group)>0 then region else ” end
,empid=case when patindex(‘%empid%’,@group)>0 then empid else 0 end
,n
from cube
where [date] between @startdate and @enddate
and region like @region
and empid like @empid
) as a
group by region,empid
order by
case when patindex(‘%desc%’,@orderby)>0 and patindex(‘%region%’,@orderby)>0 then region end desc
,case when patindex(‘%region%’,@orderby)>0 then region end
,case when patindex(‘%desc%’,@orderby)>0 and patindex(‘%empid%’,@orderby)>0 then empid end desc
,case when patindex(‘%empid%’,@orderby)>0 then empid end
,case when patindex(‘%desc%’,@orderby)>0 and patindex(‘%sumSales%’,@orderby)>0 then sum(N) end desc
,case when patindex(‘%sumSales%’,@orderby)>0 then sum(N) end
return
end
</Our Reporting Stored Procedure>
HOW IT WORKS
The syntax for order by is: ORDER BY [EXPRESSION1] [ASC|DESC], [EXPRESSION2] [ASC|DESC],
… [EXPRESSIONn] [ASC|DESC]. For example if the output of expression1 is “region” – SQL will sort the results by region. The output of an expression must be a column name. The output of expression1 cannot be “region desc”. Hence we have to write a case statement for descending and a case statement for ascending. Let’s break the following statement down: case when patindex(‘%desc%’,@orderby)>0 and patindex(‘%region%’,@orderby)>0 then region end desc. If the words “desc” and “region” are found in the @orderby parameter then output “region desc” else output “[void] desc”. When @orderby=”region”, the order by statement is translated into: ORDER BY [void] desc, region, [void] desc, [void], [void] desc, [void].
EXAMPLE OUTPUT:
exec cubesp @group=’region,empid’,@orderby=’sumSales desc’
REGION EMPID sumSales
South 12 44
West 32 36
West 31 24
North 1 18
North 2 6
East 21 4
West 33 4
South 13 2