Problem with if-statement used at Table-returned-function in SQL

Question

I have simplified my function to the following:

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns table 
as 
return 
   if @ProjectId=0
   begin 
      select TeamId from TblTeam t 
        union
      select 0 as TeamId
   end
   else
   begin
      select t.TeamId from TblTeam t
        union
      select 1 as TeamId
   end;
go

I cannot make it work.. It seems I have some syntax errors, but I cannot figure out how to make it work.. Any idea?

Answer 1

You must declare the table with a temporary name and a schema in the function declaration, then insert into it in the function:

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns @mytable table (TeamID int)  
as 
...

and then something like:

INSERT INTO @mytable 
select t.TeamId from TblTeam t
    union
select 1 as TeamId

This works especially well for functions that insert several rows into the table.

Alternatively, if you only wish to return the results of a single SELECT, you can use an inline return:

BEGIN
    RETURN (
        select t.TeamId from TblTeam t
            union
        select 1 as TeamId
    )
END

Answer 2

If you are going to use t-sql code in the function, you need to define the table in the 'returns' section, then populate it with insert statements:

create function [dbo].[UserSuperTeams](@ProjectId int) 
  returns @results table (
    TeamId int
  ) as begin

  if @ProjectId=0 begin       
    insert @results (TeamId)
      select TeamId from TblTeam t
      union      
      select 0 as TeamId   
  end   
  else begin
    insert @results (TeamId)
      select t.TeamId from TblTeam t
      union      
      select 1 as TeamId   
end;

return
end

Answer 3

As Jeremy said, or if it really is very like your simplified example you can do:

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns table 
as 
return (select TeamId from TblTeam t 
        union
        select CASE WHEN @ProjectId = 0 THEN 0 ELSE 1 END as TeamId
       )
go

(i.e. you may not have to define the table var/schema)

Answer 4

You are trying to use multi-line table valued function as a single-line (inline) one; for difference see here and here.