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

TOTAL ROW

Our sales manager requests, that when he groups by region and/or empid, that a total row be added to the bottom of the result set. We can accomplish this using many techniques. In this article I’ll demonstrate how to (a) call a stored procedure from a stored procedure and (b) share temp tables. Essentially I plan to convert the existing “groupSales” method code block into a drone. It cannot execute by itself; a master method must call upon it to execute. I will create a new method code block “groupSalesWithTotal”. This method will call upon “groupSales” once to create the body of the result set. If needed, it will call upon “groupSales” again to add the total row. Internally, I will also add a new parameter @type, which will distinguish between the “body” and the “total”.

<Our Reporting Stored Procedure>

ALTER PROCEDURE CubeSP

(

@method varchar(50)='groupSalesWithTotal'

,@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'

,@type varchar(50)=null

)

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

insert into #R (type,region,empid,sumSales)

select type=@type,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

if @method='groupSalesWithTotal' begin

create table #R (type varchar(50),region varchar(50),empid int, sumSales int)

exec CubeSp

@method       ='groupSales'

,@region       =@region

,@startdate   =@startdate

,@enddate     =@enddate

,@empid        =@empid

,@group        =@group

,@orderby      =@orderby

,@type                   ='Body'

if @group<>'all' begin

exec CubeSp

@method       ='groupSales'

,@region       =@region

,@startdate   =@startdate

,@enddate     =@enddate

,@empid        =@empid

,@group        ='all'

,@orderby      =@orderby

,@type          ='Total'

end

select  * from #R

return

end

</Our Reporting Stored Procedure>