sql try catch 的使用

USE [Forever_JT_SH]
GO
/****** Object:  StoredProcedure [dbo].[Run_Area_Report]    Script Date: 02/01/2013 12:30:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Run_Area_Report]
WITH 
EXECUTE AS CALLER
AS
DECLARE @res   INT
DECLARE @time   DATETIME
DECLARE @errorMsg   VARCHAR(1000)
DECLARE @resMsg   VARCHAR(10)
DECLARE @RunType   VARCHAR(50)
DECLARE @GUID char(36)

SET @GUID = '10dda603-44ba-47e6-8728-e8b3e862638d'
SET @time = dateadd(day, -1, getdate())


-----------------------------------  BEGIN
SET @RunType = '分年龄段自行车统计'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = Report_Region_Age @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END

----------------------------------- BEGIN
SET @RunType = '交易汇总信息'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res =  dbo.Report_Region_TotalConsumeInfo  @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
--------------------------------------- END

-----------------------------------  BEGIN
SET @RunType = '分时段自行车统计'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = dbo.Report_Region_TimeSpan  @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

 -- Run Log
insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END

-----------------------------------  BEGIN
SET @RunType = '自行车投放运营统计'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = Report_Region_BicycleThrow  @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

 -- Run Log
insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END

-----------------------------------  BEGIN
SET @RunType = '设备及故障统计'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = Report_Region_DeviceAndFault  @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

 -- Run Log
insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END

-----------------------------------  BEGIN
SET @RunType = '区域运营统计'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = Report_Region_TotalTransaction  @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

 -- Run Log
insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END

-----------------------------------  BEGIN
SET @RunType = '自行车使用率统计表'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = Report_Region_BicycleUseByDay @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END

-----------------------------------  BEGIN
SET @RunType = '网点设备及故障统计表'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = Report_Region_DeviceUseByDay @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END

-----------------------------------  BEGIN
SET @RunType = '银行卡业务收支统计表'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = Report_Region_TotalBankCardConsumeInfo @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END

-----------------------------------  BEGIN
SET @RunType = '网点交易统计报表'
SET @errorMsg = ''
SET @resMsg = ''
BEGIN TRY
  BEGIN
    EXEC @res = Report_Region_TotalCardConsumeInfo @time
     IF @res = 0
     begin
      SET @resMsg = '执行成功'
      end
    ELSE
    begin
      SET @resMsg = '执行失败'
      end
  END
END TRY
BEGIN CATCH
  SET @resMsg = '执行失败'
  SET @errorMsg = '出错行: ' + CAST(ERROR_LINE() AS VARCHAR(8)) + ', 错误码: ' + CAST(ERROR_NUMBER() AS VARCHAR(8)) + ', 消息: ' + ERROR_MESSAGE();
END CATCH

insert into  TSYs_RunLOg(C_RunApp,C_RunDesc,C_RunResult,C_RunTypeGuid,D_RunTime) 
values(@RunType,  @errorMsg,  @resMsg, @guid,getdate())
-----------------------------------  END