Error using Common Table Expression in SQL User Defined Function

Question

CREATE FUNCTION [dbo].[udfGetNextEntityID]
()
RETURNS INT
AS
BEGIN
    ;WITH allIDs AS
    (
    SELECT entity_id FROM Entity 
    UNION SELECT entity_id FROM Reserved_Entity
    )       
  RETURN (SELECT (MAX(entity_id) FROM allIDs )

END
GO

SQL isn't my strong point, but I can't work out what I'm doing wrong here. I want the function to return the largest entity_id from a union of 2 tables. Running the script gives the error:

 Incorrect syntax near the keyword 'RETURN'.

I looked to see if there was some restriction on using CTEs in functions but couldn't find anything relevant. How do I correct this?

Alexander Simonov · Accepted Answer · 2013-10-01 15:58:29Z

7

CREATE FUNCTION [dbo].[udfGetNextEntityID]()
RETURNS INT
AS
BEGIN
  DECLARE @result INT;

  WITH allIDs AS
  (
    SELECT entity_id FROM Entity 
    UNION SELECT entity_id FROM Reserved_Entity
  )       
  SELECT @result = MAX(entity_id) FROM allIDs;

  RETURN @result;

END
GO

answered Oct 1, 2013 at 15:58

Alexander Simonov

1,5841 gold badge9 silver badges15 bronze badges

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

Comments

Community · Accepted Answer · 2020-07-29 18:27:52Z

3

While you can do it, why do you need a CTE here?

  RETURN
  (
    SELECT MAX(entity_id) FROM
    (
      SELECT entity_id FROM dbo.Entity 
      UNION ALL
      SELECT entity_id FROM dbo.Reserved_Entity
    ) AS allIDs
  );

Also there is no reason to use UNION instead of UNION ALL since this will almost always introduce an expensive distinct sort operation. And please always use the schema prefix when creating / referencing any object.

edited Jul 29, 2020 at 18:27

CommunityBot

11 silver badge

answered Oct 1, 2013 at 16:12

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

3 Comments

Ashish Gupta Over a year ago

+1 Agreed. Also, for Union Vs Union All - In this case, you need the Max value and don't care If the rows are distinct. So, don't use UNION which gives distinct rows but has a performance hit. Use Union All instead. blog.sqlauthority.com/2009/03/11/…

Aaron Bertrand Over a year ago

Thanks @Ashish but isn't that essentially what I said? :-)

Ashish Gupta Over a year ago

Sure @Aaron. Because the guy asking the question said "SQL isn't my strong point". So, I wanted to supplement your answer with a link for UNION Vs UNION ALL for his information.That's it.

Ashish Gupta · Accepted Answer · 2013-10-01 16:00:36Z

1

You can not return the way your are doing from the function.

Error

Make use of a local variable and return the same.

 CREATE FUNCTION [dbo].[udfGetNextEntityID]()
    RETURNS INT
    AS
    BEGIN
      DECLARE @MaxEntityId INT;

      WITH allIDs AS
      (
        SELECT entity_id FROM Entity 
        UNION SELECT entity_id FROM Reserved_Entity
      )       
      SELECT @MaxEntityId = MAX(entity_id) FROM allIDs;

      RETURN @MaxEntityId ;

    END
 GO

answered Oct 1, 2013 at 16:00

Ashish Gupta

15.2k21 gold badges81 silver badges136 bronze badges

1 Comment

Reversed Engineer Over a year ago

But adding a local variable then means it's no longer an inline function - slower. Not saying you're wrong at all - just looking for the advantages of both CTE and inline UDF. Please see sqlservercentral.com/articles/T-SQL/91724 and stackoverflow.com/questions/1084263/…

Bhargav Rao · Accepted Answer · 2016-03-03 10:12:37Z

-1

create function tvfFormatstring (@string varchar(100))
returns @fn_table table
(id int identity(1,1),
item int)
as
begin

insert into @fn_table(item)
declare @result int 
set @string = @string+'-'
;with cte (start,number)
as
(

select 1 as start , CHARINDEX('-',@string,1) as number
union all
select number+1 as start , CHARINDEX('-',@string,number+1) as number from cte 
where number <= LEN(@string)

)

select @result = SUBSTRING(@string,start,number-start) from cte ;
return @result;

end



select * from tvfFormatstring ('12321-13542-15634')

edited Mar 3, 2016 at 10:12

Bhargav Rao

52.6k29 gold badges130 silver badges142 bronze badges

answered Mar 3, 2016 at 10:11

REVURI KIRAN

1

1 Comment

SubliemeSiem Over a year ago

Only providing code is not a correct answer. Please elaborate.

Collectives™ on Stack Overflow

Error using Common Table Expression in SQL User Defined Function

4 Answers 4

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related