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
create procedure testproc as select x,y,z from Table update Table set retreived = 'Y' go
The question I have, is what happens if the procedure executes, returns the select but the connection breaks before the batch is retreived. Does the batch still get updated?
Otherwise can anyone think of a better way to acheive this with stored procs?