SQL Server Techniques for Creating a Web Reporting Page -- Part 2

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],

… [EXPRESSIONⁿ] [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