How to rethrow the same exception in SQL Server

Question

I want to rethrow the same exception in SQL Server that has just occurred in my try block. I am able to throw same message but I want to throw 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 error, but I want functionality something like that. This raises error with error number 50000, but I want the error number to be thrown that I am passing @@error,

I want to capture this error no at the frontend.

i.e.

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

I want this functionality. which can't be achieved using raiseerror. I don't want to give custom error message at back end.

RAISEERROR should return below mentioned error when I pass ErrorNo to be thrown in catch

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

Line 14 Violation of UNIQUE KEY constraint 'UK_DomainCode'. Cannot insert duplicate key in object 'Tags.tblDomain'. The statement has been terminated.

EDIT:

What can be the drawback of not using try catch block if I want exception to be handled at frontend considering stored procedure contains multiple queries that need to be executed?

Answer 1

SQL 2012 introduces the throw statement:

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

If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised.

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