How to rethrow the same exception in SQL Server

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?

Solution 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

Solution 2:

Here is a fully functional clean code sample to rollback a series of statements if an error occurs and reports the error message.

begin try
    begin transaction;

    ...

    commit transaction;
end try
begin catch
    if @@trancount > 0 rollback transaction;
    throw;
end catch

Before SQL 2012

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();
    if @@trancount > 0 rollback transaction;
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

Solution 3:

Rethrowing inside the CATCH block (pre-SQL2012 code, use 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)