Error with CTE . Could any one help me correct the below error

Question

I am writing below function to return Suffix passing name as parameter. I made possible cases of suffix exsist in name as common table expression and trying to compare with that. Could any one explain me the proper way of doing it.

  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

Error message.

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

I see it now, you have a semi-colon at the end of your CTE declaration. You can't do that since that terminates the statement. Try this:

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

EDIT based on new information, here is a table-valued function that returns all results:

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