I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something like:
UPDATE dbo.Table SET Retrieved = 'Y' OUTPUT inserted.x, inserted.y, inserted.z; The question I have, is what happens if the procedure executes, returns the select but the connection breaks before the batch is re received Does the batch still get updated?
Otherwise can anyone think of a better way to acheive this with stored procs?
You should just do an SP, return the records, then update. If it is all within a transaction it is all or nothing, everything commits, or everything rolls back.
Interesting Sql protocol issue around SP results
Posted - 10/08/2012 : 10:42:38
