Calling stored procedure with select and update statement 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?
You can do this in a single statement using the OUTPUT clause:

UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z;


If you don't really mean to update the entire table every time:

UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z
WHERE &lt;...where clause...&gt;;