Calling stored procedure with select and update statement

Question

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?

Answer 1

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 <...where clause...>;

• blakmk: Yes, its more efficient. My question is really "is the Sql protocol intelligent enough to notice that connection has dropped before it completes the update?"
• Aaron Bertrand: It's not about efficiency, it's about being atomic. You can't get the results of the select unless the update has succeeded. This isn't true when you separate the actions into two statements. SQL Server will not send some kind of notification to your app that the select results are not valid - which wouldn't be possible anyway if the connection is severed. It also won't (and can't) roll back a select because the update failed (or never started).
• RBarryYoung: @Aaron: maybe you should add that as a request to connect? "Allow us to Rollback SELECT statements?" ;-)
• blakmk: Thanks for the response Aaron, what do you think would be the behaviour when the update succeeded but the connection got severed? would the update complete or would it rollback.