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