如何在 SQLServer 中重新引发相同的异常

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.

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.

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

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.

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

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

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.

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)

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'