[FN_ObtenerArbolEmpresa]

ALTER function [dbo].[FN_ObtenerArbolEmpresa](@empresaID int)
RETURNS TABLE
AS
RETURN
WITH  temp
AS (
        -- anchor
    SELECT  id_empresa_hijo, id_empresa_padre
    FROM    empresa_x_empresa with(nolock)
    WHERE   id_empresa_padre = @empresaID
    UNION ALL
        --recursive member
    SELECT  t.id_empresa_hijo, t.id_empresa_padre
    FROM    empresa_x_empresa AS t with(nolock)
            JOIN temp AS a
                ON t.id_empresa_padre = a.id_empresa_hijo
)
SELECT id_empresa_hijo
FROM (
	select id_empresa_hijo 
	from empresa_x_empresa with (nolock) 
	UNION ALL 
	select 0
) AS isZero
WHERE @empresaID = 0

UNION ALL

SELECT id_empresa_hijo 
FROM (
	SELECT id_empresa_hijo  
	FROM temp 
	UNION ALL
	select @empresaID
) AS notZero
WHERE @empresaID <> 0