how to create a multi-statement table valued function

Question

I am having a SQL function and I want to return a table, but I'm getting an error: A RETURN statement with a return value cannot be used in this context.

Here is my query:

 ALTER FUNCTION [dbo].[Fnc_GetParentCategories]-- 21
  ( 
@catId int
  )
   Returns table

As
 Begin

Declare @Table table(Id int identity(1,1),Category_Id int,ParentId int);
declare @cid int;
 WITH  x AS (              
 SELECT a.Category_Id, a.ParentId               
 FROM   t_Category a              
 WHERE a.Category_Id=@CatId -- enter dead node walking here              

 UNION  ALL              
 SELECT b.Category_Id, b.ParentId              
 FROM   t_Category b              
 JOIN x   ON x.Category_Id  =b.ParentId            
  )             

  insert into @Table select * from x;

 return @Table 

end

And the error is:

A RETURN statement with a return value cannot be used in this context

Can you post the error that you're getting? It would help us identify the issue faster.. — Radu Gheorghiu
– Radu Gheorghiu, Commented Jun 18, 2013 at 7:59
A RETURN statement with a return value cannot be used in this context. — Cronaldo
– Cronaldo, Commented Jun 18, 2013 at 8:00

gzaxx · Accepted Answer · 2013-06-18 08:01:12Z

1

Your query is incorrect:

ALTER FUNCTION [dbo].[Fnc_GetParentCategories]-- 21
(
    @catId int
)
Returns @Table table
(
    Id int identity(1,1),
    Category_Id int,
    ParentId int
)
AS
    Begin

    WITH  x AS
    (
         SELECT a.Category_Id, a.ParentId
         FROM   t_Category a
         WHERE a.Category_Id=@CatId -- enter dead node walking here

         UNION  ALL

         SELECT b.Category_Id, b.ParentId
         FROM   t_Category b
         JOIN x   ON x.Category_Id  =b.ParentId
    )

    insert into @Table select * from x;

    return

end

you declare your table definition just before as

answered Jun 18, 2013 at 8:01

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oleksandr Fedorenko · Accepted Answer · 2013-06-18 08:20:14Z

1

--Transact-SQL Multistatement Table-valued Function Syntax

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

Your modified function

ALTER FUNCTION [dbo].[Fnc_GetParentCategories]-- 21
 ( 
  @catId int
  )
RETURNS @Table TABLE(Id int identity(1,1),Category_Id int,ParentId int)
As
BEGIN
WITH x AS
 (              
  SELECT a.Category_Id, a.ParentId               
  FROM t_Category a              
  WHERE a.Category_Id = @CatId -- enter dead node walking here                  
  UNION ALL              
  SELECT b.Category_Id, b.ParentId              
  FROM t_Category b              
  JOIN x ON x.Category_Id = b.ParentId            
  )             
  INSERT @Table select * from x;
  RETURN
END

edited Jun 18, 2013 at 8:20

answered Jun 18, 2013 at 8:00

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

2 Comments

Cronaldo Over a year ago

Cannot perform alter on 'dbo.Fnc_GetParentCategories' because it is an incompatible object type.

Tab Alleman Over a year ago

He's right. He can't ALTER the old function into this one, he has to DROP and CREATE it, because the type of function changed.

Collectives™ on Stack Overflow

how to create a multi-statement table valued function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related