How to avoid While Loop - RBAR ?

  • Hi Experts,

    I have written a query to produce the following result

    A

    A B

    A B C

    A B C D

    A B C D E

    A B C D E F

    Query :

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

    Declare @Str char(6),@Len int,@IntitalValue int,@FinalResult Varchar(6)

    Set @Str = 'ABCDEF'

    Set @Len = len(@Str)

    set @InitialValue = 1

    while @InitialValue <= @Len

    Begin

    select @FinalResult = substring(@Str,1,@InitialValue)

    print @FinalResult

    End

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

    I dont want to use 'While Loop' becuase it uses RBAR Logic.How to avoid while loop in this situation ? I want to apply SetBased logic for the above problem.

    Experts inputs are welcome !

    karthik

  • I forgot to include

    select @InitialValue = @InitialValue + 1

    karthik

  • Hey Karthikeyan,

    Just have a Look on this Post.

    Hope, you can get idea from this.

    Cheers!

    Sandy.

    --

  • I'll show how, you explain why...

    DECLARE @Str CHAR(6)

    SET @Str = 'ABCDEF'

    SELECT LEFT(@Str,Number)

    FROM Master.dbo.spt_Values

    WHERE Type = 'P'

    AND Number BETWEEN 1 AND 6

    ORDER BY Number

    Do be advised that there are certain types of RBAR that are faster than "set based"... they're far and few between, but they do exist...

    --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)

  • Sandy (12/10/2007)


    Hey Karthikeyan,

    Just have a Look on this Post.

    Hope, you can get idea from this.

    Cheers!

    Sandy.

    Ummm... thanks Sandy, but that whole article is about how bad triangular joins are... not meant to be a fix for things like what Karthik asked for.

    --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)

  • Jeff,

    Can you explain me the logic used in your query ?

    Really i am confused. I don't understand the link between 'BETWEEN 1 AND 6' and 'LEFT (@STR,number).Also what is the use of master.dbo.spt_values table ? Why are you using here ?

    Kindly clarify my doubts.

    karthik

  • Also if i want to print A-Z in the mentioned format ,can i use the same logic or do i need to change your code ?

    karthik

  • Of course the "code" must change... but the changes I made also make it more flexible...

    DECLARE @Str VARCHAR(100)

    SET @Str = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

    SELECT LEFT(@Str,Number)

    FROM Master.dbo.spt_Values

    WHERE Type = 'P'

    AND Number BETWEEN 1 AND LEN(@Str)

    ORDER BY Number

    Go look at the spt_Values table... you'll figure it out...

    --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)

  • Thanks Jeff Moden,

    Cheers!

    Sandy.

    --

  • You bet, Sandy... thanks 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)

  • Karthik...

    What about you... you all set?

    --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)

  • Jeff,

    Can you explain me the logic ?

    karthik

  • Ok... sure...

    In order to do what you wanted, you wrote a loop to have a variable (@InitialValue) count from 1 to 6 in your first example. Then you used the content of that variable in the LEFT(@Str,@InitialValue).

    I just happen to know that spt_Values table in SQL Server 2k has a particular TYPE (Type = 'P') that contains all the whole numbers from 0 to 255. So, if I write a query like this...

    SELECT Number

    FROM Master.dbo.spt_Values

    WHERE Type = 'P'

    AND Number BETWEEN 1 AND 6

    ORDER BY Number

    ...I'll get a result that, like your loop, counts from 1 to 6...

    Number

    -----------

    1

    2

    3

    4

    5

    6

    (6 row(s) affected)

    Now, a count is a count whether it comes from a loop variable like you had or a column of numbers like I have. I used the values from the Number column to replace your variable...

    ...that's all there is to it.

    As you can see, a "list of numbers" can come in quite handy... there's a huge number of things you can do with it including parsing columns of delimited data, creating a "Title Case" function, separating alpha and numeric characters in functions, creating columns of contiguous dates, etc, etc, etc.

    Some folks call those a "Numbers" table... I call it a "Tally" table because it sounds cooler 😉 and it's used to count (tally) things. Here's how to make a Tally table that has more than the max number of characters a VARCHAR holds and can also be used to generate just over 30 years of dates...

    --===== Create and populate the Tally table on the fly

    SELECT TOP 11000 --equates to more than 30 years of dates

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    --===== Allow the general public to use it

    GRANT SELECT ON dbo.Tally TO PUBLIC

    --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)

  • So... are you happy with that answer or is there something else?

    --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)

  • Really i am happy with your answer.But...Still i have some questions on spt_values table.

    1) How many types are there like 'P' ?

    2) I did search in Google,but i didn't get clear information about the table.So can you explain me where we need to apply this table ?I mean ,as you told,In future if i faced the above kind of problem then i would use this table,like this in which situations we can use this table.

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

    declare @ServerRole nvarchar(35)

    declare @MemberName sysname

    declare srvrolemember cursor for

    select 'ServerRole' = spv.name, 'MemberName' = lgn.name

    from

    master.dbo.spt_values spv,

    master.dbo.sysxlogins lgn,

    sysusers u

    where

    spv.low = 0 and

    spv.type = 'SRV' and

    lgn.srvid IS NULL and

    spv.number & lgn.xstatus = spv.number and

    lgn.sid = u.sid and

    lgn.name <> 'sa'

    order by 'MemberName'

    for read only

    open srvrolemember

    fetch next from srvrolemember into @ServerRole, @MemberName

    while @@fetch_status = 0

    begin

    Print 'exec sp_addsrvrolemember N''' + @MemberName + ''', ' + @ServerRole

    fetch next from srvrolemember into @ServerRole, @MemberName

    end

    close srvrolemember

    deallocate srvrolemember

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

    If you see the above example , it uses spt_values table.Like this where we can use this table ?

    Can you give me the detailed information about master.dbo.spt_values table ? Also can you list out some situations that we can use this table ?

    karthik

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

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