SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Interesting Sql protocol issue around SP results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blakmk
Starting Member

45 Posts

Posted - 10/08/2012 :  10:42:38  Show Profile  Reply with Quote
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?

www.DataTao.co.uk

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/08/2012 :  15:50:27  Show Profile  Visit chadmat's Homepage  Reply with Quote
You should just do an SP, return the records, then update. If it is all within a transaction it is all or nothing, everything commits, or everything rolls back.

-Chad
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/08/2012 :  19:58:44  Show Profile  Reply with Quote
i think you should be doing select followed by an update of retrieved field as 'Y' both wrapped within a transaction

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000