Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Feb 2004
    Posts
    17

    Unanswered: Stored Procedure Programming

    Hi,

    Is there a way of looping thru a result set inside a stored proc?

    Any good resources/sites for programming stored procedures would be very much appreciated.

    Thanks



    Steve

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Books Online?

    Do you have the sql server client tools installed?

    Instead of answering that one, why don't you tell us what has to be done, and let us come up with a solution.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2005
    Posts
    28

    Wink

    Yes.

    DECLARE @local variables...
    DECLARE <cursor> CURSOR FOR SELECT ...
    OPEN <cursor>
    IF (@@CURSOR_ROWS <> 0)
    SET @loop control variable = 1

    WHILE (@loop control variable = 1)
    BEGIN
    FETCH NEXT FROM <cursor> INTO @local variables
    IF (@@FETCH_STATUS < 0)
    BEGIN
    SELECT @loop control variable = 0
    CONTINUE
    END
    .
    .
    .
    END
    CLOSE <cursor>
    DEALLOCATE <cursor>


    You can use DECLARE <cursor> CURSOR FOR SELECT... to declare a cursor. Subsequently, you can use OPEN

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you want to show them the dark side, get it correct

    WHILE @@FETCH_STATUS = 0
    BEGIN


    Luke....I....am your father.....

    Noooooooooooooooooooooooooo
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you insist upon loading the gun and handing it to him, why not go ahead and cock it too, while you are at it.

    And @Loop Control?

    Puh-lease....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2005
    Posts
    28

    Talking

    Dude!

    If you do this way, then there must be another fetch statement within the loop. It is a matter of choice...


    Thanks.... Papa....

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


    Quote Originally Posted by Brett Kaiser
    If you want to show them the dark side, get it correct

    WHILE @@FETCH_STATUS = 0
    BEGIN


    Luke....I....am your father.....

    Noooooooooooooooooooooooooo

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    *LOL* upon seeing the title of this thread, I came to watch with amusement the suggestions for using a cursor, and see that the work I came to do (aka, cursor bashing) has been done.

    So...that leaves us again at the end of Brett's first response, which is "...why don't you tell us what has to be done, and let us come up with a solution."

    Most "looping" logic (aka "cursor" logic) can be avoided.

    C'mon, bring it.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by skrishnamurthy
    If you do this way, then there must be another fetch statement within the loop. It is a matter of choice...
    No, if you follow Brett's method you need only one fetch statement inside your loop, just as in your example. But you don't have to bother with an extra variable or a call to @@CURSOR_ROWS either.
    If you look at the examples in Books Online, you'll see that they are the same method Brett is suggesting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2005
    Posts
    28
    I use @@CURSOR_ROWS to make sure that the cursor has some rows before I execute WHILE loop. I believe in checking @@FETCH_STATUS after fetching a row as opposed to using it first and then fetching data within. In this case, additional statements need to be added to take care of no data situation. Hence, it is a matter of style.



    Quote Originally Posted by blindman
    No, if you follow Brett's method you need only one fetch statement inside your loop, just as in your example. But you don't have to bother with an extra variable or a call to @@CURSOR_ROWS either.
    If you look at the examples in Books Online, you'll see that they are the same method Brett is suggesting.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Simplicity is the best coding style.

    When I come across code like you posted I just have to chuckle to myself, 'cause I know I'm going to make my client happy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Sep 2005
    Posts
    28
    You don't even know my style. Please! Simplicity does not mean no status checking for better handling. @@global variables are defined for some purpose. When you open INSENSITIVE Cursors, @@CURSOR_ROWS returns the number of rows or -1 selected depending on the value of 'cursor threshold limit'. This allows you to take appropriate action. I have been satisfying my clients for a very long time ().

    Quote Originally Posted by blindman
    Simplicity is the best coding style.

    When I come across code like you posted I just have to chuckle to myself, 'cause I know I'm going to make my client happy.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, I've seen you post about a dozen misinformed or misleading statements on the forum so far, and you've posted some code which is mediocre at best and which more than one of the veterans on this board found amusing, so yes I do think I have a good idea about your programming style.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Sep 2005
    Posts
    28

    Red face

    What a veteran? Guess who is talking. You did not even know that a database can be shrunk and stated that it is not possible and now you are talking. I am not here to fight with people like you. May be, it is time for you to read your "Perfection...." message and try to live up to that. Please do not think that you know it all. I have seen it already and this is my last posting on this site. Why? Because of people like you and I am not interested in fighting. Don't bother to post back. I will not even check any more.
    Good luck and have fun....


    Quote Originally Posted by blindman
    Well, I've seen you post about a dozen misinformed or misleading statements on the forum so far, and you've posted some code which is mediocre at best and which more than one of the veterans on this board found amusing, so yes I do think I have a good idea about your programming style.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Please show me where I said that databases cannot be shrunk. You should read more carefully, because what I said is that they cannot be shrunk below their original size. From Books Online:
    Code:
    You cannot shrink an entire database to be smaller than its original size.
    Therefore, if a database was created with a size of 10 megabytes (MB) and
    grew to 100 MB, the smallest the database could be shrunk to, assuming all the
    data in the database has been deleted, is 10 MB.
    You will not be missed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    On the contrary I will stay and take you head on professionally. Let me ask you this... If I create a database to 10MB and subsequently find that 7MB is free. Can I shrink the database to 3MB or not? The answer is right there.

    Quote Originally Posted by blindman
    Please show me where I said that databases cannot be shrunk. You should read more carefully, because what I said is that they cannot be shrunk below their original size. From Books Online:
    Code:
    You cannot shrink an entire database to be smaller than its original size.
    Therefore, if a database was created with a size of 10 megabytes (MB) and
    grew to 100 MB, the smallest the database could be shrunk to, assuming all the
    data in the database has been deleted, is 10 MB.
    You will not be missed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •