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?

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

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

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)

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.

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...

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.

The way to stop execution in a stored procedure after an error has occurred and bubble the error back to the calling program is to follow each statement that might throw an error with this code:

If @@ERROR > 0
Return

I was surprised myself to find out that execution in a stored procedure can continue after an error - not realizing this can lead to some hard to track down bugs.

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

Given that you haven't moved to 2012 yet, one way to implement the bubbling up of the original error code is to use the text message part of the exception you are (re)throwing from the catch block. Remember that it can contain some structure, for example, XML text for your caller code to parse in its catch block.

You can also create a wrapper stored procedure for the those scenarios when you want the SQL statement to be executed within the transaction and feed the error up to 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'

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.