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 |
|
tech_1
Posting Yak Master
129 Posts |
 Posted - 2013-03-01 : 12:58:07
|
using .NET here and also SPROCSif in a SPROC, an insert fails, I want to be able to rollback that transaction so none of the inserts are commited but also still raise an error back to .NEThow can I do this?i.e:quote:
INSERT INTO [Table] (cols) VALUES (vals)INSERT INTO [Table2] (COLS) VALUES (@vals) -- error raised
I want to rollback the data insert into the previous inserts but still raise an error back (same original error)ideas? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-01 : 13:26:13
|
| You should use a try/catch block in combination with begin tran/commit/rollback. Take a look at this page - it has examples of how to do that. http://msdn.microsoft.com/en-us/library/ms175976.aspx (look for examples (b) and (c))Now, if you want to rethrow the error, you can do a raiserror in the catch block - which will then be returned to .Net. If you are on SQL 2012, use throw instead of raiserror. This page has an example of raiserror within a catch block: http://msdn.microsoft.com/en-us/library/ms177497(v=sql.105).aspx |
 |
|
|
|
|
|
Transactions - rollback on error and raise error