Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arif101
Starting Member
4 Posts |
 Posted - 2012-08-16 : 07:10:04
|
| 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?ThanksArif |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-16 : 07:49:16
|
| 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. |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-08-16 : 07:57:40
|
you may want to check out snapshot isolation.http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspxThe 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 CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
|
|
|
Error Handling on Insert Stored Proc