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?

Answer 1

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

Answer 2

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.