I need to do something like this but I can't manage to find the correct syntax.
ALTER function [dbo].[FN_ObtenerArbolEmpresa] (@empresaID int)
returns table
as
BEGIN
IF (@empresaID = 0)
begin
RETURN select id_empresa_hijo from empresa_x_empresa
end
ELSE
begin
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 temp
union
select @empresaID
)
end
Actually I just want to avoid the recursion when @empresaID = 0.