Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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...>;
share|improve this answer
    
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?" –  blakmk Oct 4 '12 at 15:14
    
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). –  Aaron Bertrand Oct 4 '12 at 15:41
    
@Aaron: maybe you should add that as a request to connect? "Allow us to Rollback SELECT statements?" ;-) –  RBarryYoung Oct 4 '12 at 20:46
    
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. –  blakmk Oct 8 '12 at 10:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.