Refactor reports parse error - SS2005 doesn't

John Rogerson

I've this script snippet as part of a larger script I'm working on. It parses and executes successfully in SQL Server 2005, but SQL Refactor reports a parsing error. Has anybody any ideas how to work around this?

CREATE FUNCTION [dbo].[fn_Split] (@instring varchar(4000), @sep char(1) = ',' )
RETURNS TABLE
AS
/*-------------------------------------------------------------------+
¦ Use a recursive cte to split a delimited input string into a table ¦
+-------------------------------------------------------------------*/
RETURN
(
WITH cte (ID, ix, ipos)
AS
(
   SELECT   ID   = 1,
            ix   = 1,
            ipos = CHARINDEX(@sep, @instring)
UNION ALL
   SELECT   ID   = ID + 1,
            ix   = ipos + 1,
            ipos = CHARINDEX(@sep, @instring, ipos + 1)
     FROM   cte
    WHERE   ipos > 0
)
   SELECT   ID,
            Data = SUBSTRING(@instring, ix, CASE WHEN ipos > 0
                                                 THEN ipos - ix
                                                 ELSE 4000
                                                 END)
     FROM   cte
)
Go

Brian Donahue

Hi John,

I think this is related to a lack of support for CTEs returned by functions and stored procedures in the schema engine, which is affecting multiple products at the moment. This should be added in the next version of Refactor, provided it includes our new DLL. For your reference, the particular manifestation of this bug in SQL Refactor is SR-727.

Thanks for getting in touch!