Options
Refactor reports parse error - SS2005 doesn't
John Rogerson
Posts: 8
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
Old Tinkers never die, they just deflate slowly over time.
Comments
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!