SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_sumCompanySales]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_sumCompanySales]
GO

CREATE PROCEDURE  sp_sumCompanySales

@Parent INT

AS

SET NOCOUNT ON

DECLARE @CompanyName VARCHAR(50)

--GET THE COMPANY NAME
SELECT @CompanyName = CompanyName FROM
Companies  WHERE CompanyID = @Parent


--TRUNCATE THE TEMPORARY  TABLE IF IT EXISTS
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name ='##TEMP')
BEGIN
	DROP TABLE ##TEMP
END

--EXECUTE THE RECURSIVE STORED PROCEDURE
EXEC sp_getCompanies  @Parent

--THE RECURSIVE STORED PROCEDURE HAS INSERTED ALL THE CHILDREN
--NOW INSERT THE PARENT INTO THE TABLE
INSERT ##TEMP VALUES( @Parent)


--USING THE LIST OF COMPANIES IN ##TEMP
--PERFORM AGGREGATION
SELECT  @CompanyName , SUM(Sales)
FROM ##TEMP a , Sales b
WHERE a.CompanyID  =b.CompanyID






GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO