How to recover the same exception in sql server

Question

I want to recover the same exception in sql server that occurred in my try block. I can throw the same message, but I want to throw the same error.

BEGIN TRANSACTION BEGIN TRY INSERT INTO Tags.tblDomain (DomainName, SubDomainId, DomainCode, Description) VALUES(@DomainName, @SubDomainId, @DomainCode, @Description) COMMIT TRANSACTION END TRY BEGIN CATCH declare @severity int; declare @state int; select @severity=error_severity(), @state=error_state(); RAISERROR(@@Error,@ErrorSeverity,@state); ROLLBACK TRANSACTION END CATCH 

RAISERROR(@@Error, @ErrorSeverity, @state);

This line will show an error, but I want something like this. This causes an error with error number 50000, but I want the erron number to be selected, that I pass @@error ,

I want to fix this error no at frontend

i.e.

 catch (SqlException ex) { if ex.number==2627 MessageBox.show("Duplicate value cannot be inserted"); } 

I want this functionality. which cannot be achieved by leveling up. I do not want to give a custom error message on the back panel.

RAISEERROR should return below the specified error when I pass ErrorNo to throw in catch

 Msg 2627, Level 14, State 1, Procedure spOTest_DomainInsert, 

Line 14 Violation of UNIQUE KEY 'UK_DomainCode' constraint. Cannot insert duplicate key in object 'Tags.tblDomain. Application completed.

EDIT:

What could be the drawback of using the catch try block if I want the exception to be handled in the front, given the stored procedure, it contains several queries that need to be executed

Answer 1

The following is an example of a fully functional clean code for rolling back a number of statements if an error occurs and report an error.

 begin try begin transaction; ... commit transaction; end try begin catch declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int; select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); rollback transaction; raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState); end catch 

Answer 2

SQL 2012 introduces a throw statement:

http://msdn.microsoft.com/en-us/library/ee677615.aspx

If the THROW statement is specified without parameters, it should appear inside the CATCH block. This raises an excluded exception.

 BEGIN TRY BEGIN TRANSACTION ... COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW END CATCH 

Answer 3

I think your choice:

• Don't catch the mistake (let it bubble up)
• Raise custom

At some point, SQL is likely to present a reraise command or the ability to catch only certain errors. But for now, use a workaround. Unfortunately.

Answer 4

Repeating inside the CATCH block (code before SQL2012, use the THROW statement for SQL2012 and later):

 DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE(), @ErrorNumber int = ERROR_NUMBER(), @ErrorSeverity int = ERROR_SEVERITY(), @ErrorState int = ERROR_STATE(), @ErrorLine int = ERROR_LINE(), @ErrorProcedure nvarchar(200) = ISNULL(ERROR_PROCEDURE(), '-'); SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + @ErrorMessage; RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine) 

Answer 5

You cannot: only the engine can throw errors less than 50,000. All you can do is throw an exception that looks like this ...

See my answer here, please

The question here used client-side transactions to do what he wanted, I think it's a little silly ...

Answer 6

Ok, this is a workaround ... :-)

 DECLARE @Error_Number INT BEGIN TRANSACTION BEGIN TRY INSERT INTO Test(Id, Name) VALUES (newID(),'Ashish') /* Column 'Name' has unique constraint on it*/ END TRY BEGIN CATCH SELECT ERROR_NUMBER() --RAISERROR (@ErrorMessage,@Severity,@State) ROLLBACK TRAN END CATCH 

If you notice a catch block, it does not raise an error, but returns the actual error number (as well as a transaction rollback). Now in your .NET code, instead of throwing an exception, if you use ExecuteScalar (), you will get the actual error number you want and show the corresponding number.

 int errorNumber=(int)command.ExecuteScalar(); if(errorNumber=<SomeNumber>) { MessageBox.Show("Some message"); } 

Hope this helps,

EDIT: - Just a note. If you want to get the number of records affected and try to use ExecuteNonQuery, the above solution may not work for you. Otherwise, I think this will suit you. Tell me.

Answer 7

How to stop execution in a stored procedure after an error occurs and output the error back to the calling program to follow each statement that may cause an error using this code:

 If @@ERROR > 0 Return 

I was surprised to learn that execution in a stored procedure may continue after an error - not understanding, this can lead to some difficult error tracking.

This type of error handling parallels (pre.Net) Visual Basic 6. We look forward to the Throw command in SQL Server 2012.

Answer 8

Given that you have not yet moved to 2012, one way to implement buffering of the source error code is to use the portion of the exception text message that you selected (throw) from the catch block. Remember that it may contain some structure, such as XML text for your caller code for analysis in a catch block.

Answer 9

You can also create a wrapper stored procedure for these scenarios when you want the SQL statement to execute in a transaction and give an error before your code.

 CREATE PROCEDURE usp_Execute_SQL_Within_Transaction ( @SQL nvarchar(max) ) AS SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION EXEC(@SQL) COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int SELECT @ErrorMessage = N'Error Number: ' + CONVERT(nvarchar(5), ERROR_NUMBER()) + N'. ' + ERROR_MESSAGE() + ' Line ' + CONVERT(nvarchar(5), ERROR_LINE()), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() ROLLBACK TRANSACTION RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH GO -- Test it EXEC usp_Execute_SQL_Within_Transaction @SQL = 'SELECT 1; SELECT 2' EXEC usp_Execute_SQL_Within_Transaction @SQL = 'SELECT 1/0; SELECT 2' EXEC usp_Execute_SQL_Within_Transaction @SQL = 'EXEC usp_Another_SP' 

Answer 10

From a design point of view, what is the point of throwing exceptions with the original error numbers and user messages? To some extent, it violates the interface contract between applications and the database. If you want to catch the original errors and process them in a higher code, do not process them in the database. Then, when you catch the exception, you can change the message presented to the user to whatever you want. I would not do this because it makes your database code “wrong”. As others have said, you should define a set of custom error codes (above 50,000) and throw them instead. Then you can block the integrity problems ("Duplicate values ​​are not allowed") separately from potential business problems - "Zip code is invalid", "No rows matching the criteria", etc.