CTE 错误。任何人都可以帮我纠正以下错误

Question

我正在编写下面的函数来返回后缀，传递名称作为参数。我将后缀可能存在于名称中的情况作为公用表表达式并尝试与之进行比较。任何人都可以向我解释一下正确的做法吗？

  Alter function S (@Name varchar(100))
returns varchar(25)
as 
begin
declare @Suffix varchar(25)
WITH SearchTerms(Term)
     AS (SELECT ' I '
         UNION ALL
         SELECT ' II '
         UNION ALL
         SELECT ' III '
         UNION All
         SELECT ' MD '
          UNION All
         SELECT ' M.D '
          UNION All
         SELECT ' M.D. '
          UNION All
         SELECT ' D.O '
           UNION All
         SELECT ' D.O. '
           UNION All
         SELECT ' DO '
         )  ;

 set @Suffix = (select Term from SearchTerms where Charindex(Term,@Name) > 0)
 return @Suffix

 end

错误信息。

Msg 319, Level 15, State 1, Procedure S, Line 6
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, 
an xmlnamespaces clause or a change tracking context clause, the previous statement must 
be terminated with a semicolon.

Answer 1

我现在看到了，您的 CTE 声明的末尾有一个分号。你不能这样做，因为这会终止语句。试试这个：

ALTER FUNCTION dbo.S(@Name varchar(100))
RETURNS VARCHAR(25)
AS
BEGIN
    DECLARE @Suffix VARCHAR(25);

    WITH SearchTerms(Term) AS 
    (
      SELECT ' I '
      UNION ALL SELECT ' II '
      UNION ALL SELECT ' III '
      UNION ALL SELECT ' MD '
      UNION ALL SELECT ' M.D '
      UNION ALL SELECT ' M.D. '
      UNION ALL SELECT ' D.O '
      UNION ALL SELECT ' D.O. '
      UNION ALL SELECT ' DO '
    )
    SELECT @Suffix = Term 
        FROM SearchTerms 
        WHERE CHARINDEX(Term, ' ' + @Name + ' ') > 0;

    RETURN (LTRIM(RTRIM(@Suffix)));
END
GO

根据新信息编辑，这是一个返回所有结果的表值函数：

CREATE FUNCTION dbo.T
(
    @Name VARCHAR(100)
)
RETURNS TABLE
AS
    RETURN
    (
        SELECT 
            Term = CONVERT(VARCHAR(25), LTRIM(RTRIM(Term)))
        FROM
        (
            SELECT Term = ' I '
            UNION ALL SELECT ' II '
            UNION ALL SELECT ' III '
            UNION ALL SELECT ' MD '
            UNION ALL SELECT ' M.D '
            UNION ALL SELECT ' M.D. '
            UNION ALL SELECT ' D.O '
            UNION ALL SELECT ' D.O. '
            UNION ALL SELECT ' DO '
        ) AS Terms
        WHERE CHARINDEX(Term, ' ' + @Name + ' ') > 0
    );
GO

SELECT Term FROM dbo.T('Terry Allen MD III');