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?

AdaTheDev · Accepted Answer · 2009-12-10 14:32:56Z

6

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

edited Dec 10, 2009 at 14:32

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

answered Dec 10, 2009 at 13:53

Ray

21.9k6 gold badges51 silver badges67 bronze badges

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

5 Comments

Stavros Over a year ago

this will be parsed, but when executed, I get an error: "The last statement included within a function must be a return statement"

AdaTheDev Over a year ago

There's also an "end" missing. Just add: "return; end" to the end of what Ray has given

AdaTheDev Over a year ago

I've updated Ray's answer, just to add the return statement in

Ray Over a year ago

oops - should check my own syntax :(

Tomas Kubes Over a year ago

For me, this is very slow when used as subquery.

Jeremy Smyth · Accepted Answer · 2009-12-10 13:52:05Z

2

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

answered Dec 10, 2009 at 13:52

Jeremy Smyth

23.6k2 gold badges57 silver badges66 bronze badges

Comments

AdaTheDev · Accepted Answer · 2009-12-10 13:54:34Z

1

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)

answered Dec 10, 2009 at 13:54

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

2 Comments

Stavros Over a year ago

my example was really simplified and this solution cannot be applied :(

AdaTheDev Over a year ago

Ah ok. In which case, +1 to @Jeremy's solution. I'll leave my answer as it does highlight the potential use of an alternative dependent on the exact scenario

Ángel Ibáñez · Accepted Answer · 2018-02-12 10:51:18Z

0

this code is working for me :

DROP FUNCTION IF EXISTS [dbo].[test] 
GO

CREATE FUNCTION [dbo].[TEST] 
(
	@ACTIVEONLY bit
)
RETURNS @TST TABLE (column1 char)
AS
BEGIN
	IF @ACTIVEONLY = 1
	BEGIN
		INSERT INTO @TST(column1) VALUES('A')
	END
	ELSE
	BEGIN
		INSERT INTO @TST(column1) VALUES('B')
	END
	RETURN
END
GO

SELECT * FROM [dbo].[TEST](1)
GO

SELECT * FROM [dbo].[TEST](0)
GO

edited Feb 12, 2018 at 10:51

answered Jan 11, 2018 at 15:29

Ángel Ibáñez

3791 silver badge6 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

5 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related