Home Forums Article Discussions Article Discussions by Author Discuss content posted by Amit Gaur Convert String to a Table using CTE RE: Convert String to a Table using CTE

  • December 25, 2009 at 9:08 pm

    #1096504

    Ulises Telcontar (12/25/2009)

    MAXRECURSION cannot be used on UDFs.

    I beg to differ...

    CREATE FUNCTION dbo.MaxRecursionTest ()

    RETURNS INT

    AS

    BEGIN

    DECLARE @Return INT

    SELECT TOP 1 @Return = 1 FROM Sys.Objects OPTION (MAXRECURSION 0)

    RETURN @Return

    END

    The WHILE methods are not the best performers either, using a table of numbers is. Find

    some examples here: http://stackoverflow.com/questions/753582/implementing-and-

    applying-a-string-split-in-t-sql

    There are normally two problems with links like that one... First, they never explain how a

    Numbers table works. Second, they use a bloody While loop to build it... not a real

    confidence builder that the author really knows anything about set based programming.

    Here's about the only article I've ever seen that explains not only how to build one and

    what it can be used for, it also explains how it actually works to replace certain forms of

    loops. This particular author (I know him fairly well :-D) also shows how to build one

    using a While loop but only to show how slow a While loop is and why it should be

    avoided even for one-off code. Here's the link...

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)