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>