T-SQL中是否有与C的“throw”;等效的方法来重新抛出异常?

你可以使用raiserror。从 MSDN documentation 关于RaISError:

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return 
    -- error information about the original error that 
    -- caused execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

编辑:

这和C不是一回事 throw 或 throw ex . 正如@henrikstaunpoulsen指出的那样,新错误中没有原始错误号(raiserror限制了它可以使用的数字)。您必须使用某种约定并解析消息中的信息(如果可用)。

msdn有一篇文章 Using TRY...CATCH in Transact-SQL 我用一些代码创建了下面的测试:

use test;
GO

IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL
    DROP PROCEDURE usp_RethrowError;
GO

CREATE PROCEDURE usp_RethrowError AS
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        @ErrorState,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
GO

PRINT 'No Catch'
DROP TABLE XXXX

PRINT 'Single Catch'
BEGIN TRY
    DROP TABLE XXXX
END TRY
BEGIN CATCH
    EXEC usp_RethrowError;
END CATCH;

PRINT 'Double Catch'
BEGIN TRY
    BEGIN TRY
        DROP TABLE XXXX
    END TRY
    BEGIN CATCH
        EXEC usp_RethrowError;
    END CATCH;
END TRY
BEGIN CATCH
    EXEC usp_RethrowError;
END CATCH;

产生以下输出:

No Catch
Msg 3701, Level 11, State 5, Line 3
Cannot drop the table 'XXXX', because it does not exist or you do not have permission.
Single Catch
Msg 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25
Error 3701, Level 11, State 5, Procedure -, Line 7, Message: Cannot drop the table 'XXXX', because it does not exist or you do not have permission.
Double Catch
Msg 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25
Error 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25, Message: Error 3701, Level 11, State 5, Procedure -, Line 16, Message: Cannot drop the table 'XXXX', because it does not exist or you do not have permission.

在SQL 2012中,他们添加了新的throw关键字,该关键字还可用于重新引发异常。

USE tempdb;
GO
CREATE TABLE dbo.TestRethrow
(    ID INT PRIMARY KEY
);
BEGIN TRY
    INSERT dbo.TestRethrow(ID) VALUES(1);
--  Force error 2627, Violation of PRIMARY KEY constraint to be raised.
    INSERT dbo.TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH

    PRINT 'In catch block.';
    THROW;
END CATCH;

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

下面是我在回滚事务后用来重新引发异常的内容。这也提供了错误的行号信息。

BEGIN TRY
    BEGIN TRANSACTION    -- Start the transaction

    -- Do your work here

    -- Commit the transaction
    COMMIT TRANSACTION

END TRY

BEGIN CATCH
    -- There was an error, rollback the transaction
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    -- Raise an error with the details of the exception
    DECLARE @ErrorMessage nvarchar(2048)
    DECLARE @ErrorProcedure nvarchar(128)
    DECLARE @ErrorState int
    DECLARE @ErrorLine int
    DECLARE @ErrorSeverity int

    SET @ErrorProcedure = ERROR_PROCEDURE()
    SET @ErrorLine = ERROR_LINE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState = ERROR_STATE()
    SET @ErrorMessage = ''

    IF @ErrorProcedure IS NOT NULL
        SET @ErrorMessage = @ErrorMessage + @ErrorProcedure + ' ';

    IF @ErrorLine IS NOT NULL
        SET @ErrorMessage = @ErrorMessage + '[Line ' + CAST(@ErrorLine as nvarchar) + '] ';

    SET @ErrorMessage = @ErrorMessage + ERROR_MESSAGE()

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

为了防止在多个catch场景中重复过程信息/错误/行号,我使用了类似的过程,稍微修改如下:

IF @Error_Procedure <> OBJECT_NAME(@@PROCID)    
    BEGIN
        RAISERROR('[Procedure: %s]: Nest Level: %d; Line: %d; Error Number: %d; Message: %s',@Error_Severity,@Error_State,@Error_Procedure, @NestLevel, @Error_Line, @Error_Number, @Error_Message)
    END
ELSE
    BEGIN
        RAISERROR(@Error_Message,@Error_Severity,@Error_State)
    END

因此,如果我们已经捕获并重新引发了这个SP的错误,我们不会重复添加额外的信息,因此在外部范围,我们只看到最初重新引发的错误。

在上面的示例中,双catch输出将与单catch输出相同。我还将嵌套级别包含在错误消息中,以帮助调试。

可以使用raiseError引发异常

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

我通常使用以下方法:

DECLARE @Outcome as bit
DECLARE @Error as int

BEGIN TRANSACTION 

-- *** YOUR TSQL TRY CODE HERE **** 


-- Capture the TSQL outcome. 
SET @Error = @@ERROR 

-- Set the Outcome to be returned to the .NET code to successful
SET @Outcome = 1


IF @Error <> 0
    BEGIN   
        -- An Error was generate so we invoke ROLLBACK
        ROLLBACK
        -- We set the Outcome to be returned to .Net to unsuccessful
        SET @Outcome = 0
    end

ELSE
    BEGIN
        -- The transaction was successful, invoke COMMIT
        COMMIT
    END



-- Returning a boolean value to the .NET code
Select @Outcome as Outcome