Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Processing row by row - is this safe way?

    Hello,

    I need to process table row by row, gettin params and calling stored proc...

    Is this below written way safe?

    set rowcount 1
    select @ln1 = a, @ln2 = b, @ln3 = c from x_table
    set rowcount 0

    while @ln_exists
    begin

    exec some_proc with params @ln1, @ln2, @ln3

    set rowcount 1
    delete table x_table
    set rowcount 0

    select @ln1 = a, @ln2 = b, @ln3 = c from x_table
    select @ln_exists = @@rowcount


    end

    My question is: will always the same row deleted from x_table that was selected in command select before?

    (if no --- is there other effective way getting rows from x_table ( that has no identity column )...

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    You should use a cursor for this.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Martijnvs
    You should use a cursor for this.
    I Agree, and add error checking.

  4. #4
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Yes, I would do it with cursor myself

    But I am concerned about this:

    ...
    set rowcount 1
    select @ln1 = a, @ln2 = b, @ln3 = c from x_table
    set rowcount 0
    ...
    ...
    ...
    set rowcount 1
    delete table x_table
    set rowcount 0
    ...

    My question: Will be really the same row deleted that was selected?
    Last edited by Musil David; 10-24-07 at 05:13.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    delete table x_table
    There's no WHERE clause...
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by Musil David
    Yes, I would do it with cursor myself

    But I am concerned about this:

    ...
    set rowcount 1
    select @ln1 = a, @ln2 = b, @ln3 = c from x_table
    set rowcount 0
    ...
    ...
    ...
    set rowcount 1
    delete table x_table
    set rowcount 0
    ...

    My question: Will be really the same row deleted that was selected?
    .If you take a cursor and very every row with it, if a row qualifies for deletion, you can delete it.
    But as Georgev says, add a where-clause to your delete-statement, or your whole table will be empty.

    You should read up on how to use cursors in the documentation, it contains a lot of useful information.
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Musil David
    My question is: will always the same row deleted from x_table that was selected in command select before?

    (if no --- is there other effective way getting rows from x_table ( that has no identity column )...
    Will the same row be deleted from x_table that was previously selected? No (for reasons stated above).
    Is there an effective way of getting rows from _table to be deleted? Yes.

    Why don't you outline what you are trying to achieve and explain what criteria must be met for a row to be deleted?
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    As an example, from what I understand of your various posts, you want something like this:
    Code:
    declare cursor cur_dosomething for
    select a, b, c from x_table
    for update
    
    open cur_dosomething
    fetch cur_dosomething into @in1, @in2, @in3
    
    while (@@sqlstatus = 0)
    begin
       exec some_proc with params @in1, @in2, @in3
       delete from x_table 
       where a = @in1
       and b = @in2
       and c = @in3
    
       fetch cur_dosomething into @in1, @in2, @in3
    end
    
    close cur_dosomething
    deallocate cursor cur_dosomething
    Include this in a stored procedure or something, and make sure all variables are properly declared.
    I'm not crazy, I'm an aeroplane!

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Martijnvs, it occurs to me that your cursor
    1. cycles through every record
    2. executes a sproc with fetched values
    3. deletes the record

    This implies that every record will be deleted by the end of the loop, so I suggest you take it out of the loop and use a simple
    Code:
    DELETE FROM x_table
    At the end.
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by georgev
    Martijnvs, it occurs to me that your cursor
    1. cycles through every record
    2. executes a sproc with fetched values
    3. deletes the record

    This implies that every record will be deleted by the end of the loop, so I suggest you take it out of the loop and use a simple
    Code:
    DELETE FROM x_table
    At the end.
    It isn't intended as a final solution. It should be adapted to Musil Davids specific needs. It can be used as a guide to how he can use a cursor.
    If he want's only a particular group of records to be deleted he can change the delete-statement or add a Where-clause.
    I'm not crazy, I'm an aeroplane!

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies, confused you with the OP! No harm done, eh?
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by georgev
    Apologies, confused you with the OP! No harm done, eh?
    Not at all .
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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