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?