在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的限制，但没有找到任何相关内容。我该如何进行更正？

- Marcus K

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

- Alexander Simonov

Answer 2

虽然你可以这样做，但为什么需要在这里使用CTE呢？

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

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

- Aaron Bertrand

---

+1 同意。此外，在 Union Vs Union All 的情况下，您需要最大值，并且不关心行是否不同。因此，请勿使用 UNION，它会提供不同的行但会影响性能。请改用 Union All。http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/ - Ashish Gupta

谢谢@Ashish，但这不是我说的吗？ :-) - Aaron Bertrand

当然，@Aaron。因为提问者说“SQL不是我的强项”。所以，我想通过一个链接来补充你的答案，让他了解UNION和UNION ALL的区别。就这样。 - Ashish Gupta

Answer 3

你不能从函数中以你当前的方式返回。

利用本地变量并返回相同的变量。

 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

- Ashish Gupta

---

但是添加一个本地变量意味着它不再是内联函数 - 速度较慢。并不是说你错了 - 只是在寻找CTE和内联UDF的优点。请参见http://www.sqlservercentral.com/articles/T-SQL/91724/和https://dev59.com/MEjSa4cB1Zd3GeqPEEo2 - Reversed Engineer

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

- REVURI KIRAN

---

仅提供代码不是正确的答案。请详细说明。 - SubliemeSiem