SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

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

CREATE PROCEDURE sp_getCompanies

@Parent INT

AS

SET NOCOUNT ON
DECLARE @CompanyID INT


--GET ALL CHILDREN OF THE ROOT INTO CURSOR
--A CURSOR WILL BE RECURSIVELY CREATED FOR EACH RECORD IN CURSOR

	DECLARE  compCur  CURSOR LOCAL  FOR 
	SELECT CompanyID FROM CompanyHierarchy 
	WHERE  ParentCompanyID = @Parent

OPEN compCur 
FETCH NEXT FROM compCur  INTO @CompanyID


--CREATE A GLOBAL TEMPORARY TABLE IF NOT ALREADY EXISTING
IF NOT EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name ='##TEMP')
BEGIN
	CREATE TABLE ##TEMP(
	CompanyID INT
	)
END


WHILE  (@@FETCH_STATUS = 0)
BEGIN

	--INSERT THE CHILD COMPANIES INTO A TEMPORARY TABLE
	INSERT  ##TEMP VALUES(@CompanyID)
	--EXECUTE THIS STORED PROCEDURE RECURSIVELY FOR EACH RECORD IN CURSOR
	EXEC sp_getCompanies @CompanyID
       	FETCH NEXT FROM compCur  INTO @CompanyID
END


CLOSE  compCur
DEALLOCATE  compCur





















GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO