Answered by:
Transaction rollback

Question
-
I have sql stored procedures in which I included a begin try - begin tran - commit tran - end try - begin catch - rollback tran - end catch. In my c# winform I used to include a try catch in the ado.net and when it went to the catch I let the user know that the transaction had an error. Now though if the stored procedure properly executes the rollback on the error then it doesn't catch in c# since it finished successfully (through a rollback). So even when really there was an error and it rolled back the user doesn't know about it. How can I fix that?
Also, sometimes there is an error in a stored procedure before it even starts the begin tran. For example if the user inputs a number that is too high for an int 32 into a table valued parameter so then it does catch in c# and doesn't go through the rest of the procedure. But, I would like one uniform way of finding errors and letting the user know via c#. How can I do that?
Debra has a question
Monday, April 11, 2016 7:21 PM
Answers
-
You need to re-raise the error in the CATCH block to let the application know that things went south.
I have an article on my web site which gives you an introduction to error handling in SQL Server:
http://www.sommarskog.se/error_handling/Part1.htmlErland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Marked as answer by Sam ZhaMicrosoft contingent staff Saturday, April 23, 2016 2:31 PM
Monday, April 11, 2016 10:04 PM
All replies
-
You have 2 ways
a) Transactions via C#
b) FROM SQL: USE Try catch with begin and end tran
In the try catch on SQL use RAISEERROR, this would ensure an error is thrown from SQL,and should be returned as sql error to C#
BEGIN TRAN
BEGIN TRYPRINT'Commit Transaction Ran Successfully'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Inside Catch'
RAISERROR (N'The current database ID is:%d, the database name is: %s.', 10, -- Severity.---- Increase the severity 1, -- State. @DBID, -- First substitution argument. @DBNAME); -- Second substitution argument.
--Use this inside message for meaning full error SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() as ErrorState,ERROR_PROCEDURE() as ErrorProcedure,ERROR_LINE() as ErrorLine,ERROR_MESSAGE() as ErrorMessage;
ROLLBACK TRAN
END CATCHAbhinav http://bishtabhinav.wordpress.com/
Monday, April 11, 2016 7:28 PM -
In your begin catch /end catch statement simply throw the error back. This way your C# code will always get the SqlException back.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles- Proposed as answer by davidbaxterbrowneMicrosoft employee Monday, April 11, 2016 9:47 PM
Monday, April 11, 2016 8:03 PM -
You need to re-raise the error in the CATCH block to let the application know that things went south.
I have an article on my web site which gives you an introduction to error handling in SQL Server:
http://www.sommarskog.se/error_handling/Part1.htmlErland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Marked as answer by Sam ZhaMicrosoft contingent staff Saturday, April 23, 2016 2:31 PM
Monday, April 11, 2016 10:04 PM -
How do you do that? Is it enough to just write THROW in the catch? I have sql 2008 and I saw that throw is for 2012 and up.
Debra has a question
- Edited by Debra has a question Tuesday, April 12, 2016 4:20 PM
Tuesday, April 12, 2016 3:51 PM -
Check out this article http://social.technet.microsoft.com/wiki/contents/articles/20002.structured-error-handling-mechanism-in-sql-server-2012.aspx
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articlesTuesday, April 12, 2016 3:58 PM