2

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.

2 Answers 2

1

You need to define the table you want to return. For Example:

ALTER  FUNCTION [dbo].[Split](@sText varchar(8000), @sDelim varchar(20) = '')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(2000))
AS
BEGIN
...
Sign up to request clarification or add additional context in comments.

1 Comment

thanks! i tried before that but forgot to insert values on the @retArray table.
0

Looks like you are missing an END at the end of your Query. The you have one to end the function, but not one to end the ELSE statement.

1 Comment

The first syntax error appears in the first "BEGIN" after "as"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.