Post reply

Recursive loop

  • December 28, 2011 at 6:51 pm

    #252616

    Hello all,

    I was trying to figure out a way to write this code using a cte, Could someone help me with this.

    declare @i int

    set @i = 01

    --print @i

    declare @t table

    (tex Varchar(10))

    While @i < 6

    BEGIN

    insert into @t

    select 'ABC'+convert(varchar(2),@i)

    set @i = @i+1

    END

    Select * from @t

    Thanks in advance.

  • December 28, 2011 at 7:34 pm

    #1426300

    How about this?

    --::Create a tally table---------------------------

    select top 1000 identity(int, 1,1)[N]

    into #tempTally

    from master..syscolumns a, master..syscolumns b

    --::Do the trick here------------------------------

    select 'ABC' + convert(varchar(2),N)

    from #tempTally

    where N < 6

    ---------------------------------------------------

    You can check Jeff Moden[/url]'s article[/url] about the tally table to be amazed more.:-D

    "Often speak with code not with word,
    A simple solution for a simple question"

  • December 29, 2011 at 10:36 pm

    #1426776

    Thank you mhike2hale!

  • December 30, 2011 at 12:32 am

    #1426784

    Hi,

    with cte this code will work fine.

    with cte as

    (select 1 as i

    union all

    select i+1 from cte where i<5

    )

    select 'ABC'+CAST(i as varchar) from cte

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • December 30, 2011 at 5:36 am

    #1426903

    malleswarareddy_m (12/30/2011)

    Hi,

    with cte this code will work fine.

    with cte as

    (select 1 as i

    union all

    select i+1 from cte where i<5

    )

    select 'ABC'+CAST(i as varchar) from cte

    Please read the following article for why you shouldn't create a "counting CTE" like that...

    http://qa.sqlservercentral.com/articles/T-SQL/74118/

    --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)
    Intro to Tally Tables and Functions

  • December 30, 2011 at 6:46 am

    #1426928

    Jeff Moden (12/30/2011)

    malleswarareddy_m (12/30/2011)

    Hi,

    with cte this code will work fine.

    with cte as

    (select 1 as i

    union all

    select i+1 from cte where i<5

    )

    select 'ABC'+CAST(i as varchar) from cte

    Please read the following article for why you shouldn't create a "counting CTE" like that...

    http://qa.sqlservercentral.com/articles/T-SQL/74118/

    If you're wondering why Jeff has posted this - because the number of rows is so small - consider the folks who would have read this thread and used the same flawed method to create a much larger result set. rCTE's are an incredibly expensive tool to use for something simple and cheap like row generation.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • December 30, 2011 at 6:51 am

    #1426931

    Thanks Malleswarareddy!

  • December 30, 2011 at 6:55 am

    #1426934

    Thanks you Jeff!

  • December 30, 2011 at 6:57 am

    #1426936

    Thanks Chris!

  • December 30, 2011 at 8:46 pm

    #1427150

    ChrisM@home (12/30/2011)

    Jeff Moden (12/30/2011)

    malleswarareddy_m (12/30/2011)

    Hi,

    with cte this code will work fine.

    with cte as

    (select 1 as i

    union all

    select i+1 from cte where i<5

    )

    select 'ABC'+CAST(i as varchar) from cte

    Please read the following article for why you shouldn't create a "counting CTE" like that...

    http://qa.sqlservercentral.com/articles/T-SQL/74118/

    If you're wondering why Jeff has posted this - because the number of rows is so small - consider the folks who would have read this thread and used the same flawed method to create a much larger result set. rCTE's are an incredibly expensive tool to use for something simple and cheap like row generation.

    Absolutely correct, Chris. Not only that, but consider the following quote from the article (which is backed up by the tests in the "Resources" section of the article)...

    Ok... let's put this into terms that everyone can understand. At a count of 5, the rCTE is more than 200% slower than the other methods. At a count of 8,000, the rCTE is about 3,300% slower than the slowest of the other 3 methods.

    I just can't see intentionally making code twice as slow even for such small rowcounts as "5". 🙂

    --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)
    Intro to Tally Tables and Functions

  • December 30, 2011 at 9:02 pm

    #1427151

    CELKO (12/30/2011)

    We get data from the outside world; we do not like to create it. We go to that world and use a procedural language to build the data you want then insert it as a completed set.

    Gosh, that's frequently not true, Joe. For example, it's usually easier to build test data and special sequences in SQL Server than to build it in the "outside world" and then either import that data or stuff the data into the server. Building such data inside the server also saves on the I/O "pipe".

    --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)
    Intro to Tally Tables and Functions

  • December 30, 2011 at 9:11 pm

    #1427152

    mhike2hale (12/28/2011)

    How about this?

    --::Create a tally table---------------------------

    select top 1000 identity(int, 1,1)[N]

    into #tempTally

    from master..syscolumns a, master..syscolumns b

    --::Do the trick here------------------------------

    select 'ABC' + convert(varchar(2),N)

    from #tempTally

    where N < 6

    ---------------------------------------------------

    You can check Jeff Moden[/url]'s article[/url] about the tally table to be amazed more.:-D

    I don't know who you are but welcome aboard and thank you for the honorable mention. 🙂

    --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)
    Intro to Tally Tables and Functions

  • December 30, 2011 at 9:13 pm

    #1427153

    ssc_san (12/30/2011)

    Thanks you Jeff!

    You bet. Thank you for taking the time for the feedback. 🙂

    --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)
    Intro to Tally Tables and Functions

  • December 31, 2011 at 1:35 am

    #1427187

    CELKO (12/30/2011)

    Is this an exercise? It is a bad approach to SQL and RDBMS.

    The purpose of SQL is to store , retrieve and maintain the integrity of data. It is a declarative langue, so we hate to use recursive CTEs (which is a cursor and loop), if-hen begin-end, and while loops.

    I wrote an SQL puzzle book that is full of "things we should never do in SQL" just to see if we can do it in SQL. It is fun and makes you think. But we do not do it in real code!

    We get data from the outside world; we do not like to create it. We go to that world and use a procedural language to build the data you want then insert it as a completed set.

    So you started marketing your books as well. Appreciate it 😛

  • December 31, 2011 at 5:56 am

    #1427205

    This is not an exercise Celko, I am a SQL newbie. Thank you for the comment and suggesting the book.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply