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?

score 7 · Accepted Answer · answered Oct 01 '13 at 15:58

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

score 3 · Answer 2 · edited Jul 29 '20 at 18:27

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 '20 at 18:27

Community

1
1

answered Oct 01 '13 at 16:12

Aaron Bertrand

272,866
37
466
490

+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. http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/ – Ashish Gupta Oct 01 '13 at 16:29
Thanks @Ashish but isn't that essentially what I said? :-) – Aaron Bertrand Oct 01 '13 at 16:34
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 Oct 01 '13 at 17:18

score 1 · Answer 3 · answered Oct 01 '13 at 16:00

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 01 '13 at 16:00

Ashish Gupta

14,869
20
75
134

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 http://www.sqlservercentral.com/articles/T-SQL/91724/ and http://stackoverflow.com/questions/1084263/can-an-inline-table-valued-udf-outperform-the-equivalent-scalar-udf-in-a-select – Reversed Engineer May 21 '15 at 14:38

score -1 · Answer 4 · edited Mar 03 '16 at 10:12

-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 03 '16 at 10:12

Bhargav Rao

50,140
28
121
140

answered Mar 03 '16 at 10:11

REVURI KIRAN

1

Only providing code is not a correct answer. Please elaborate. – SubliemeSiem Mar 03 '16 at 10:15

Error using Common Table Expression in SQL User Defined Function

4 Answers4