how to rethrow same exception in sql server

Question

I want to rethrow same exception in sql server that has been occured 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 erron number to be thrown that i am passing @@error,

I want to capture 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 can't be achieved using raiseerror. I dont 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

I think your choices are:

• Dont catch the error (let it bubble up)
• Raise a custom one

At some point, SQL will probably introduce a reraise command, or the ability to catch only certain errors. But for now, use a workaround. Sorry.

Answer 2

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 note the catch block, It is not raising the error but returning the actual error number (and also would rollback the transaction). Now in your .NET code, instead of catching the exception, if you use ExecuteScalar(), you get the actual error number you want and show the appropriate 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 trying to use ExecuteNonQuery, the above solution may not work for you. Otherwise, I think It would suit what you need. Let me know.

Answer 3

You can't: only the engine can throw errors less than 50000. All you can do is throw an exception that looks like it...

See my answer here please

The questioner here used client side transactions to do what he wanted which I think is a wee bit silly...

Answer 4

From a design point of view, what is the point of throwing exceptions with original error numbers and custom messages? To some extent it breaks the interface contract between applications and the database. If you want to catch original errors and handle them in higher code, don't handle them in the database. Then when you catch an exception you can change the message presented to the user to anything you want. I would not do it though, because it makes your database code hmm 'not right'. As others said you should define a set of your own error codes (above 50000) and throw them instead. Then you can hanle integrity issues ('Duplicate values are not allowed') separately from potential business issues - 'Zip code is invalid', 'No rows were found matching the criteria' and so on.