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 C开发者_Python百科harindex(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.
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');
精彩评论