SQL2005之try、catch,事物,存储过程（范例）

来源：互联网发布：大数据新闻节目编辑：程序博客网时间：2024/06/06 01:50

--TRY,CATCH 示例
BEGIN TRY
    SELECT 1/0AS val;
END TRY
BEGIN CATCH
   --返回错误号码
    SELECTERROR_NUMBER() AS ErrorNumber;
    --错误描述
    SELECTERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

--TRY,CATCH结合事物示例
BEGIN TRY
    BEGINTRANSACTION
    ALTER TABLETest_Table DROP COLUMN bbb;
    COMMITTRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACKTRANSACTION;
    SELECTERROR_NUMBER() AS ErrorNumber;
    SELECTERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

--TRY,CATCH结合事物和存储过程示例
ALTER procedure [dbo].[de]
AS
BEGIN
BEGIN TRY
    BEGINTRANSACTION;
    ALTER TABLETest_Table DROP COLUMN bbb;
    COMMITTRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACKTRANSACTION;
    SELECTERROR_NUMBER() AS ErrorNumber;
    SELECTERROR_MESSAGE() AS ErrorMessage;
END CATCH
END

--调用存储过程也可以写为：dbo.de
EXEC de;
GO