For loop in sql server

  • Do we have for loop or for each statement in SQL Server?

    I tried to use the for loop in my stored procedure but I haven't found it's working and it is throwing error.

    Thank You

  • It's "While ...", but I would recommend that you figure out how to write set-based code instead of relying on loop-based techniques. The performance difference is about an order of magnitude.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • venki (6/22/2008)


    Do we have for loop or for each statement in SQL Server?

    I tried to use the for loop in my stored procedure but I haven't found it's working and it is throwing error.

    Venki,

    Tell us what you're trying to do with the loop... lots of things can be done without one and will usually be much, much more performant.

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

  • Venki,

    In SSIS you get for each loops which do all kinds of things, and really assist with managing the loads into your environment.

    BUT (and this is the big but), none of these are actually row based loops, and ultimately they will perform set based operations.

    Agree with the above, post what you are trying to do, While and cursors are swearwords on the RDBMS

    ~PD

  • I got a doubt when I am trying to create a Stored Procedure.

    I am pulling the data i.e recordset by written a query from different tables.

    I supposed to modify the data by taking each row and each column.

    When I searched in Google, I haven't get any link for this. It is giving links to Oracle. That's why I have asked this question.

    Now I used Cursors and while loop and completed the task.

    Thanks a lot for your suggestions.

    Thank You

  • venki (6/23/2008)


    I got a doubt when I am trying to create a Stored Procedure.

    I am pulling the data i.e recordset by written a query from different tables.

    I supposed to modify the data by taking each row and each column.

    When I searched in Google, I haven't get any link for this. It is giving links to Oracle. That's why I have asked this question.

    Now I used Cursors and while loop and completed the task.

    Thanks a lot for your suggestions.

    I don't know for sure because your description of what you're trying to do is very generic... but I believe you've made a mistake by using a Cursor and While loop instead of some good set based code. Good luck...

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

  • venki (6/23/2008)


    ...

    Now I used Cursors and while loop and completed the task.

    Thanks a lot for your suggestions.

    ...

    :angry:

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Venki,

    If your info is not sensitive, post your code and .dtsx file, and we will see what we can do.

    ESPECIALLY, since you have SSIS, cursors should be forbidden

    ~PD

  • Yes, but what can I do. Dead line has came up. I have to submit it yesterday that's why I have created the stored procedure and I executed it in executeSQL task.

    Thank You

  • Agreed, you do whatever it takes to get the job done.

    HOWEVER, now that you have implemented, why not go back and have a look at fixing it the right way?

    Post the dtsx if you can

  • pduplessis (6/23/2008)


    Venki,

    If your info is not sensitive, post your code and .dtsx file, and we will see what we can do.

    ESPECIALLY, since you have SSIS, cursors should be forbidden

    ~PD

    OK I will send it. I will send the stored procedure in document. There is nothing in the dtsx file. only one ExecuteSQL task. In that I have given a query as EXEC sp_venkiSP.

    If it is possible to delete then immediatly delete this.

    Thank You

  • Pduplessis,

    Have you checked my Stored Procedure?

    Thank You

  • That's what happens when you "send" it to just one person, Venki... why don't you post the procedure here so we can all lend a hand... 😉

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

  • Hi,

    Your suggestions are always welcome. No body responded for this one yesterday that's why I deleted it.

    I have kept it for halfen hour and taken back. Now I am keeping again and will not delete it.

    In the execute SQL task nothing is there. I have given only

    EXEC sp_TransferIH

    Thank You

  • Wow... lots of RBAR there 😀

    For starters, you can replace the following WHILE loop...

    declare @cnt int

    declare @UnitCount int

    set @cnt=1

    set @UnitCount=1

    while @cnt<len(@s_Table_PkgNumber)

    begin

    if(substring(@s_Table_PkgNumber,@cnt,1)=', ')

    begin

    set @UnitCount=@UnitCount+1

    end

    set @cnt=@cnt+1

    End

    ... with this...

    SET @UnitCount = LEN(@s_Table_PkgNumber)-LEN(REPLACE(@s_Table_PkgNumber,',',''))+1

    That one is really important because once you've fixed that, column can be produced just using a simple select which means you can convert the Cursor into a simple SELECT. That means that you can very easily do INSERT/SELECTS instead of using a cursor and things will be very much faster.

    The following...

    if @s_Quote_Type=' '

    set @s_Quote_Type=Null

    ... can simply be replace by ...

    SET @s_Quote_Type = NULLIF(@s_Quote_Type,' ')

    I'm on my way to work and I'll try to take a look at this in more detail tonight. In the mean time... experiment with the code suggestions I've made so far...

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

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

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