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

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:

SQL不是我的强项，但我无法弄清楚我在这里做错了什么。我希望函数从2个表的并集中返回最大的entity_id。运行脚本会出错：

 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?

我查看了在函数中使用CTE是否有一些限制，但找不到任何相关的东西。我该如何纠正？

4 个解决方案

#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

#2

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

虽然你可以做到，但为什么你需要CTE呢？

  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.

此外，没有理由使用UNION而不是UNION ALL，因为这几乎总会引入昂贵的不同排序操作。并且在创建/引用任何对象时请始终使用模式前缀。

#3

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

你无法从函数中返回你的行为方式。

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

#4

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')