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

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

 

--TRY,CATCH结合事物 示例
BEGIN TRY
      BEGIN TRANSACTION
      ALTER TABLE Test_Table DROP COLUMN bbb;
      COMMIT TRANSACTION;
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION;
      SELECT ERROR_NUMBER() AS ErrorNumber;
      SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

 

--TRY,CATCH结合事物和存储过程 示例
ALTER procedure [dbo].[de]
AS
BEGIN
BEGIN TRY
      BEGIN TRANSACTION;
      ALTER TABLE Test_Table DROP COLUMN bbb;
      COMMIT TRANSACTION;
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION;
      SELECT ERROR_NUMBER() AS ErrorNumber;
      SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END

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