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)
 Error Handling on Insert Stored Proc		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arif101
Starting Member

4 Posts
Posted - 08/16/2012 :  07:10:04  Show Profile  Reply with Quote
Hi,

I have created a stored procedure that inserts lines within a table depending on the paramters passed through to it. I need to add error handling to it so that if 1 of the inserts fails for what ever reason then the stored doesn't commit the changes leading up to the failure. I know a way to acheive this is with BEGIN TRAN at the start, ROLLBACK TRAN on any errors and COMMIT TRAN on success however I am concerned that this would lock out the table for the period in which the stored procedure is running. The stored procedure will be called throughout the day in multiples and inserting to a live system.

Does anyone know a way that I can add error handling to the stored procedure without risking locking out the live tables whilst the inserts are taking place?

Thanks
Arif

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 08/16/2012 :  07:49:16  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Not if you want to roll back the changes.
You need to accumulate the changes to a table then do the insert in a single statement.
That will either succeed or fail - no need for a transaction.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3437 Posts
Posted - 08/16/2012 :  07:57:40  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
you may want to check out snapshot isolation.
http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx
The basic idea is your writes and reads should be non-blocking as the read operation will only read data for the transactional state it starts in.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
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