sql - 在 SQL 用户定义函数中使用公用表表达式时出错

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 不是我的强项，但我无法弄清楚我在这里做错了什么。我希望该函数从 2 个表的联合返回最大的entity_id。运行脚本出现错误:

 Incorrect syntax near the keyword 'RETURN'.

我查看了在函数中使用 CTE 是否存在一些限制，但找不到任何相关内容。我该如何纠正这个问题？

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