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)
 Transactions - rollback on error and raise error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

105 Posts

Posted - 03/01/2013 :  12:58:07  Show Profile  Reply with Quote
using .NET here and also SPROCS

if 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 .NET

how 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
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 03/01/2013 :  13:26:13  Show Profile  Reply with Quote
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
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