This question already has answers here:
Is it possible to execute code in SQL Server when a user aborts a procedure? (1 answer)
How to use SET XACT_ABORT ON the right way (3 answers)
Closed 24 days ago.
I have a main stored procedure in a SQL Server database. Inside this main procedure, I've called some other procedures, and within some of those procedures, I've called additional stored procedures.
I would like to know how to handle or rollback a transaction when an error occurs in one of the inner stored procedures. Alternatively, how can the main procedure detect if an error has occurred in the inner procedures?
CREATE PROCEDURE [Csm].[ReceptionSelectViews]
@IsRead bit,
@Years nvarchar(4)
AS
BEGIN
EXECUTE [Csm].[ReceptionSelectViews1]
EXECUTE [Csm].[ReceptionSelectViews2]
EXECUTE [Csm].[ReceptionSelectViews3]
ENDThe second levels procedure. at the end of following stored procedure we have called another procedure and our errors mostly occurs inside of this procedure.
CREATE PROCEDURE [Csm].[ReceptionSelectViews1]
(@IsChecked BIT = NULL)
AS
BEGIN
SET NOCOUNT ON;
IF (@IsChecked IS NOT NULL)
BEGIN
SELECT
Csm.GooglePlace.GPSHours, Csm.GooglePlace.GPSLong,
Csm.GooglePlace.GPSLat, Csm.GooglePlace.GPSName,
Csm.GooglePlace.State, Csm.GooglePlace.City,
Csm.GooglePlace.Country, Csm.GooglePlace.Address,
Csm.GooglePlace.PostCode, Csm.GooglePlace.FK_RepairManID,
Csm.RepairMan.ID, Csm.RepairMan.Name,
Csm.RepairMan.Family, Csm.RepairMan.Tell,
Csm.RepairMan.Mobile, Csm.RepairMan.EmailAddress,
Csm.RepairMan.MeliCode, Csm.RepairMan.Status,
Csm.RepairMan.Active, Csm.RepairMan.Ranking,
Csm.RepairMan.FK_CustomerSex, Csm.RepairMan.NumberCertificate,
Csm.RepairMan.FatherName, Csm.RepairMan.LocationRegisterCertificate,
Csm.RepairMan.DateOfBirth,
Csm.RepairMan.LocationOfBirth,
Csm.RepairMan.StateSoldering, Csm.RepairMan.StateMarriage,
Csm.RepairMan.Sex, Csm.RepairMan.NumberChild,
Csm.RepairMan.LastDegree, Csm.RepairMan.UniversityField,
Csm.GooglePlace.Pelak, City_1.CName AS CityName,
Csm.MeliCodeType.Subject AS MeliCodeTypeSubject,
City_2.CName AS StateName
FROM
Hrm.City
RIGHT OUTER JOIN
Csm.GooglePlace ON Hrm.City.CID = Csm.GooglePlace.FK_CityID
LEFT OUTER JOIN
Hrm.City AS City_2 ON Hrm.City.CParentID = City_2.CID
RIGHT OUTER JOIN
Csm.RepairMan
LEFT OUTER JOIN
Csm.MeliCodeType ON Csm.RepairMan.FK_MeliCodeTypeID = Csm.MeliCodeType.ID
ON Csm.GooglePlace.FK_RepairManID = Csm.RepairMan.ID
LEFT OUTER JOIN
Hrm.City AS City_1 ON Csm.GooglePlace.FK_CityID = City_1.CID
ORDER BY
Csm.RepairMan.ID
END
ELSE
BEGIN
EXECUTE [Csm].[ReceptionSelectViews4] -- Mostly we have error in this stored procedure . . .
END
ENDAnd the last procedure:
CREATE PROCEDURE [Csm].[ReceptionSelectViews4]
AS
DECLARE @EfficiencePerformanceDay tinyint,
@EfficiencePerformanceMinute smallint,
@StatuteNumber NVARCHAR(10),
@Mission smallint,
@LeaveAbsentTime int,
@EnterHour time(7),
@ExitHour time(7),
@EnterHourThursday time(7),
@ExitHourThursday time(7),
@EmployeePerformanceOneDay SMALLINT,
@TeloranceOverTime TINYINT,
@TeloranceLeave TINYINT,
@ISRequestForTelorance BIT,
@WorkHouseCode tinyint,
@StartTimeLunch time(7),
@TimeLunch smallint,
@SEnterHourRamadanMonth time(7),
@SExitHourRamadanMonth time(7),
@SThursdayEnterHourRamadanMonth time(7),
@SThursdayExitHourRamadanMonth time(7),
@SFromDateRamadan date,
@SToDateRamadan date,
@PerformanceDate NVARCHAR(10),
@DayPerformance tinyint,
@MonthPerformance tinyint,
@StatuteType tinyint,
@FromDay tinyint,
@ToDay tinyint,
@PerformanceDateBeforMax NVARCHAR(30) ,
@EmployTypeName NVARCHAR(50),
@WorkHouseName NVARCHAR(50),
@PFName NVARCHAR(50),
@PLName NVARCHAR(50),
@OrganizationPartCaption NVARCHAR(100),
@PartCaption NVARCHAR(50),
@LeaveUnPaid SMALLINT,
@LeaveSick SMALLINT,
@LeaveGuard SMALLINT,
@LeaveType tinyint,
@LeaveTemp SMALLINT,
@JobCode NVARCHAR(10),
@Date Date,
@DateCursor Date,
@TimeCursor Time(7),
@OverTime SMALLINT,
@StartDate DATE,
@StartTime TIME(7),
@LeaveTime SMALLINT,
@Leave SMALLINT,
@CDay TINYINT,
@MDays SMALLINT,
@LeaveMinute SMALLINT,
@LeaveUnPaidMinute SMALLINT,
@Absent SMALLINT,
@HasLeave BIT,
@SDate NVARCHAR(10),
@EDate NVARCHAR(10),
@EndDate DATE,
@CountDay tinyint,
@ShiftType tinyint
SELECT PCode, PPart INTO #tPersonnel
FROM Hrm.Personnel
LEFT JOIN Hrm.Statute s ON s.SNumber = (SELECT max(st.SPersonnelCode)+'/'+ cast(MAX(cast ( substring(st.SNumber,charindex('/',st.SNumber)+1,2) as float))as nvarchar(2))
FROM Hrm.Statute st
WHERE st.SPersonnelCode = PCode AND
st.SPersonnelPart = PPart
group by st.SPersonnelCode)
AND s.SPersonnelCode = PCode AND s.SPersonnelPart = PPart
WHERE
((PCode IN (select item from dbo.ConvertListItemsToTable(@PersonnelCode, ',')) AND
PPart IN (select item from dbo.ConvertListItemsToTable(@PersonnelPart, ','))) OR @PersonnelCode = '-1')
AND (s.SEmployTypeCode IN (select item from dbo.ConvertListItemsToTable(@EmployeeTypeCode, ','))
OR @EmployeeTypeCode = '-1')
AND (s.SWorkhouseCode IN (select item from dbo.ConvertListItemsToTable(@WorkHCode, ','))
OR @WorkHCode = '-1')
AND ( Hrm.OrganizationPartFind(s.SOrganizationHeadID) IN (select item from dbo.ConvertListItemsToTable(@OrganizationPartID, ','))
OR @OrganizationPartID = '-1')
AND ( s.SCostStationCode IN (select item from dbo.ConvertListItemsToTable(@CostStation, ','))
OR @CostStation = '-1')
AND ( s.SWorkhouseCode ) IN (select PartCode from Hrm.Part_UsersRelation where UserCode = @UserCode)
ORDER BY CAST(PCode AS INT) ASC
DECLARE @yearandMonth VARCHAR(10) = CAST(@Year AS NVARCHAR(4)) + '/' + (CASE WHEN LEN(CAST(@Month AS NVARCHAR(2))) = 1 THEN '0' + CAST(@Month AS NVARCHAR(2)) ELSE CAST(@Month AS NVARCHAR(2)) END)
DECLARE @FromDatemonth DATE = dbo.ShamsiToMiladi(@yearandMonth+'/01')
DECLARE @Todatemonth DATE =dbo.ShamsiToMiladi( dbo.ShamsiDateAdd('mm',1,@yearandMonth+'/01'))
WHILE ((SELECT COUNT(*) FROM #tPersonnel) > 0)
BEGIN
SET @EfficiencePerformanceDay = NULL
SET @EfficiencePerformanceMinute = NULL
SET @StatuteNumber = NULL
SET @Mission = NULL
SET @LeaveAbsentTime = NULL
SET @EnterHour = NULL
SET @ExitHour = NULL
SET @EnterHourThursday = NULL
SET @ExitHourThursday = NULL
SET @EmployeePerformanceOneDay = NULL
SET @TeloranceOverTime = NULL
SET @TeloranceLeave = NULL
SET @ISRequestForTelorance = NULL
SET @WorkHouseCode = NULL
SET @StartTimeLunch = NULL
SET @TimeLunch = NULL
SET @SEnterHourRamadanMonth = NULL
SET @SExitHourRamadanMonth = NULL
SET @SThursdayEnterHourRamadanMonth = NULL
SET @SThursdayExitHourRamadanMonth = NULL
SET @SFromDateRamadan = NULL
SET @SToDateRamadan = NULL
SET @PerformanceDate = NULL
SET @DayPerformance = NULL
SET @MonthPerformance = NULL
SET @StatuteType = NULL
SET @FromDay = NULL
SET @ToDay = NULL
SET @PerformanceDateBeforMax = NULL
SET @EmployTypeName = NULL
SET @WorkHouseName = NULL
SET @PFName = NULL
SET @PLName = NULL
SET @OrganizationPartCaption = NULL
SET @PartCaption = NULL
SET @LeaveUnPaid = NULL
SET @LeaveSick = NULL
SET @LeaveGuard = NULL
SET @LeaveType = NULL
SET @LeaveTemp = NULL
SET @JobCode = NULL
SET @Date = NULL
SET @DateCursor = NULL
SET @TimeCursor = NULL
SET @OverTime = NULL
SET @StartDate = NULL
SET @StartTime = NULL
SET @LeaveTime = NULL
SET @Leave = NULL
SET @CDay = NULL
SET @MDays = NULL
SET @LeaveMinute = NULL
SET @LeaveUnPaidMinute = NULL
SET @Absent = NULL
SET @HasLeave = NULL
SET @SDate = NULL
SET @EDate = NULL
SET @EndDate = NULL
SET @CountDay = NULL
set @ShiftType=null
SET @PersonnelCode = (SELECT TOP(1)PCode FROM #tPersonnel)
SET @PersonnelPart = (SELECT TOP(1)PPart FROM #tPersonnel)
SELECT * INTO #tOnline FROM Hrm.EmployeePerformanceOnline
WHERE
([Date] >= @FromDatemonth )
AND ([Date] < @Todatemonth)
AND PersonnelCode = @PersonnelCode
AND ([Date] >= @FromDate OR '2009-08-23 12:48:55.000' = COALESCE(@FromDate, '2009-08-23 12:48:55.000'))
AND ([Date] <= @ToDate OR '2009-08-23 12:48:55.000' = COALESCE(@FromDate, '2009-08-23 12:48:55.000'))
AND AddManualRow != 2
and IsDelete !=1
SELECT @StatuteNumber = @PersonnelCode+'/'+ cast(MAX(cast ( substring(SNumber,charindex('/',SNumber)+1,2) as float))as nvarchar(2))
FROM [Hrm].[Statute] s
WHERE [SPersonnelCode] = @PersonnelCode
AND SUBSTRING(dbo.MiladiToShamsi_10(s.SPerformanceDate), 1,7) <= (CAST(@Year AS NVARCHAR(4)) + '/' + CAST((CASE WHEN LEN(CAST(@Month AS NVARCHAR(2))) = 1 THEN '0' + CAST(@Month AS NVARCHAR(2)) ELSE CAST(@Month AS NVARCHAR(2)) END)AS NVARCHAR(2)))
SELECT @PLName = p.PLName , @PFName = p.PFName
FROM Hrm.Personnel p
WHERE p.PCode = @PersonnelCode AND p.PPart = @PersonnelPart
SELECT @WorkHouseCode = SWorkhouseCode, @PerformanceDate = dbo.MiladiToShamsi_10(SPerformanceDate),
@JobCode = SJobCode,
@StatuteType = (SELECT STIssueTime FROM Hrm.StatuteType WHERE STID = SStatuteTypeID),
@EmployTypeName = (SELECT CSICaption FROM Hrm.PersonnelStudyInfoDetail WHERE CSICode = SEmployTypeCode AND CSIKind = SEmployTypeKind),
@WorkHouseName = (SELECT CSICaption FROM Hrm.PersonnelStudyInfoDetail WHERE CSICode = SWorkhouseCode AND CSIKind = SWorkhouseKind),
@OrganizationPartCaption = (SELECT oh.OHCaption FROM Hrm.OrganizationHead oh
WHERE oh.OHID =
(SELECT Hrm.OrganizationPartFind(s.SOrganizationHeadID) FROM Hrm.Statute s
WHERE s.SNumber = @StatuteNumber AND s.SPersonnelCode = SPersonnelCode AND s.SPersonnelPart = SPersonnelPart)),
@PartCaption = (SELECT PCaption FROM Hrm.Part p WHERE p.PCode = SPersonnelPart),
@ShiftType=SShiftTypeID
FROM Hrm.Statute
WHERE SNumber = @StatuteNumber AND SPersonnelCode = @PersonnelCode
IF (CAST(SUBSTRING(@PerformanceDate, 6, 2) AS TINYINT) = @Month)
BEGIN
IF (@StatuteType = 1)
BEGIN
SET @FromDay = CAST(SUBSTRING(@PerformanceDate, 9, 2) AS INT)
SET @ToDay = CASE WHEN @Month <= 6 THEN 31
WHEN @Month = 12 AND @Year % 4 <> 3 THEN 29
ELSE 30
END
END
ELSE IF(@StatuteType = 3)
BEGIN
SELECT @PerformanceDateBeforMax = dbo.MiladiToShamsi_10(SPerformanceDate)
FROM Hrm.Statute
WHERE SNumber = (SELECT @PersonnelCode+'/'+ cast(MAX(cast ( substring(SNumber,charindex('/',SNumber)+1,2) as float))as nvarchar(2)) FROM Hrm.Statute WHERE SNumber <> @StatuteNumber AND SPersonnelCode = @PersonnelCode AND SPersonnelPart = @PersonnelPart)
AND SPersonnelCode = @PersonnelCode AND SPersonnelPart = @PersonnelPart
IF (CAST(SUBSTRING(@PerformanceDateBeforMax, 6, 2) AS TINYINT) = @Month)
SET @FromDay = CAST(SUBSTRING(@PerformanceDateBeforMax, 9, 2) AS INT)
ELSE
SET @FromDay = 1
SET @ToDay = CAST(SUBSTRING(@PerformanceDate, 9, 2) AS INT)-1
END
END
IF (@FromDay IS NULL AND @ToDay IS NULL)
BEGIN
SET @FromDay = 1
SET @ToDay = CASE WHEN @Month <= 6 THEN 31
WHEN @Month = 12 AND @Year % 4 <> 3 THEN 29
ELSE 30
END
END
SELECT @TeloranceLeave = STeloranceLeave, @TeloranceOverTime = STeloranceOverTime,
@ISRequestForTelorance = SISRequestForTelorance,
@EmployeePerformanceOneDay = SEmployeePerformanceOneDay,
@EnterHour = s.SEnterHour, @EnterHourThursday = s.SEnterHourThursday,
@ExitHour = s.SExitHour, @ExitHourThursday = s.SExitHourThursday,
@StartTimeLunch = s.SStartTimeLunch, @TimeLunch = s.SLunchTime,
@SEnterHourRamadanMonth = SEnterHourRamadanMonth, @SExitHourRamadanMonth = SExitHourRamadanMonth,
@SThursdayEnterHourRamadanMonth = SThursdayEnterHourRamadanMonth,
@SThursdayExitHourRamadanMonth = SThursdayExitHourRamadanMonth,
@SFromDateRamadan = SFromDateRamadan, @SToDateRamadan = SToDateRamadan
FROM Hrm.Setting s
WHERE s.SWorkHouse = @WorkHouseCode
SET @DayPerformance = SUBSTRING(@PerformanceDate, 9, 2)
SET @MonthPerformance = SUBSTRING(@PerformanceDate, 6, 2)
IF (@StatuteNumber IS NOT NULL)
BEGIN
SELECT * INTO #tMission FROM Hrm.DayMission(@PersonnelCode, @Year, @Month)
create TABLE #CalendarHoliday(CDay tinyint)
IF (@ShiftType<>3)
INSERT INTO #CalendarHoliday(CDay)
SELECT CDay FROM Hrm.CalendarHoliday(@DayPerformance, @StatuteType, @Year, @Month,
@WorkHouseCode, @MonthPerformance, @FromDay, @ToDay, 2)
ELSE
INSERT INTO #CalendarHoliday(CDay) (SELECT 0)
SELECT @CountDay = COUNT(CDay) FROM #CalendarHoliday
WHERE CDay NOT IN (SELECT DaysOfMonth FROM #tMission)
SET @EfficiencePerformanceDay = ISNULL((SELECT COUNT(DaysOfMonth) FROM #tMission), 0) +
ISNULL((SELECT COUNT(*) FROM (SELECT [Date] FROM #tOnline WHERE SUBSTRING(dbo.MiladiToShamsi_10([Date]), 9, 2) NOT IN (SELECT CDay FROM #CalendarHoliday) AND IsDelete = 0
AND (SELECT COUNT(onl.[Date]) FROM #tOnline onl WHERE #tOnline.[Date] = onl.[Date] AND SUBSTRING(dbo.MiladiToShamsi_10(onl.[Date]), 9, 2) NOT IN (SELECT CDay FROM #CalendarHoliday) AND onl.IsDelete = 0
GROUP BY onl.[Date]) % 2 = 0
GROUP BY [Date]) AS aa), 0)
IF (@ShiftType<>3)
SET @EfficiencePerformanceDay +=
ISNULL(CASE WHEN @CountDay = 1 AND (SELECT COUNT(CDay) FROM #CalendarHoliday) = 0 THEN 0 ELSE @CountDay END, 0)
print '@EfficiencePerformanceDay' print @EfficiencePerformanceDay
SELECT @Mission = COUNT(DaysOfMonth) FROM #tMission WHERE MMissionType = 1
SET @LeaveGuard = 0
SET @LeaveTemp = 0
IF (@ShiftType<>3)
BEGIN
select @OverTime = SUM(Amount) FROM Hrm.fn_OverTimePersonnel(@PersonnelCode, @Year, @Month, @FromDate, @ToDate, @StatuteNumber)
END
INSERT INTO Hrm.DaysAbsent (DaysOfMonth, UserCode)
(SELECT DaysOfMonth, @UserCode
FROM Hrm.DaysOfMonth as dom
WHERE dom.DaysOfMonth NOT IN(SELECT SUBSTRING(dbo.MiladiToShamsi_10([Date]), 9, 2)
FROM Hrm.EmployeePerformanceOnline
WHERE PersonnelCode = @PersonnelCode
AND AddManualRow != 2
and ([Date] >= @FromDatemonth )
AND ([Date] < @Todatemonth)
)
AND dom.DaysOfMonth NOT IN (SELECT CDay FROM #CalendarHoliday)
)
DELETE FROM Hrm.DaysAbsent WHERE DaysOfMonth IN (SELECT DaysOfMonth FROM #tMission)
DECLARE @LeaveSickPerformance TINYINT
SELECT * INTO #LeavePersonnel FROM Hrm.LeavePersonnel(@PersonnelCode, @Month, @Year, @WorkHouseCode, @UserCode, @StatuteNumber, 0)
SELECT @Leave = ISNULL(SUM(Amount), 0) FROM #LeavePersonnel WHERE TypeLeave IN (0, 7)
SELECT @LeaveSick = ISNULL(SUM(Amount), 0) FROM #LeavePersonnel WHERE TypeLeave = 1
SELECT @LeaveSickPerformance = ISNULL(SUM(Amount), 0) FROM #LeavePersonnel WHERE TypeLeave = 8
SELECT @LeaveUnPaid = ISNULL(SUM(Amount), 0) FROM #LeavePersonnel WHERE TypeLeave = 2
SELECT @Absent = ISNULL(Amount, 0) FROM #LeavePersonnel WHERE TypeLeave = 5
SET @EfficiencePerformanceDay = @EfficiencePerformanceDay +
ISNULL((SELECT SUM(Amount) FROM #LeavePersonnel WHERE TypeLeave = 3 AND LUStartTime = -1), 0) +
ISNULL((SELECT SUM(Amount) FROM #LeavePersonnel WHERE TypeLeave = 6), 0) +
ISNULL((SELECT Amount / @EmployeePerformanceOneDay FROM #LeavePersonnel WHERE TypeLeave = 7), 0)
-
ISNULL((SELECT SUM(Amount) FROM #LeavePersonnel WHERE TypeLeave = 4), 0)
PRINT @EfficiencePerformanceDay
BEGIN TRY
DROP TABLE #tDayCalendarBefor
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
DROP TABLE #LeavePersonnel
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
DROP TABLE #CalendarHoliday
END TRY
BEGIN CATCH
END CATCH
SET @LeaveMinute = ISNULL(@Leave % @EmployeePerformanceOneDay, 0)
SET @Leave = ISNULL(@Leave / @EmployeePerformanceOneDay, 0)
SET @LeaveUnPaidMinute = @LeaveUnPaid % @EmployeePerformanceOneDay
SET @LeaveUnPaid = @LeaveUnPaid / @EmployeePerformanceOneDay
SET @LeaveSick = @LeaveSick / @EmployeePerformanceOneDay
DECLARE @EfficiencePerformanceDayInsurance TINYINT
SET @EfficiencePerformanceDay = @EfficiencePerformanceDay + (@LeaveGuard / @EmployeePerformanceOneDay)
SET @EfficiencePerformanceDayInsurance = @EfficiencePerformanceDay - @LeaveSickPerformance
SELECT @Absent += COUNT(DaysOfMonth) FROM Hrm.DaysAbsent WHERE UserCode = @UserCode
DELETE FROM Hrm.DaysAbsent WHERE UserCode = @UserCode
SET @Absent +=
(CASE WHEN @MonthPerformance = @Month AND @FromDay IS NOT NULL AND @ToDay IS NOT NULL THEN CAST(@ToDay AS INT) + 1 - CAST(@FromDay AS INT)
WHEN @MonthPerformance = @Month AND @StatuteType = 3 THEN @DayPerformance - 1
WHEN @MonthPerformance = @Month THEN (CASE WHEN @Month <= 6 THEN 31
WHEN @Month = 12 AND @Year % 4 <> 3 THEN 29
ELSE 30
END) - (@DayPerformance-1)
WHEN @Month <= 6 THEN 31
WHEN @Month = 12 AND @Year % 4 <> 3 THEN 29
ELSE 30
END) -
(@Absent + @EfficiencePerformanceDay + @LeaveSick + @LeaveUnPaid - @LeaveSickPerformance)
SELECT @LeaveAbsentTime = SUM(LeaveTime) * 2
FROM Hrm.LeaveHourWithoutRequest(@Year, @Month, NULL, NULL, @PersonnelCode, @UserCode)
IF (@LeaveAbsentTime IS NOT NULL)
BEGIN
SET @Leave += (@LeaveMinute + @LeaveAbsentTime) / @EmployeePerformanceOneDay
SET @LeaveMinute = (@LeaveMinute + @LeaveAbsentTime) % @EmployeePerformanceOneDay
END
----------------------------------------------------------------------------
END
IF ((SELECT COUNT(*) FROM #tOnline) > 0 OR @Mission > 0 OR @Leave > 0 OR @LeaveSick > 0 OR @LeaveMinute > 0 OR @LeaveUnPaid > 0 OR @LeaveUnPaidMinute > 0)
BEGIN
INSERT INTO [Hrm].[EmployeePerformanceTempReports]
([EPPersonnelPart]
,[EPPersonnelCode]
,[EPActiveYear]
,[Month]
,[EfficiencePerformanceSalary]
,[EfficiencePerformanceDayInsurance]
,[WorkFriday]
,[OverTime]
,[EPMissionReal]
,[PaidLeave]
,[UnPaidLeave]
,[EPSickLeaveReal]
,[Absent]
,[EmployTypeName]
,[WorkHouseName]
,[PartCaption]
,[PFName]
,[PLName]
,[OrganizationPartCaption]
,[UserCode]
,[WorkHouseCode])
VALUES
(@PersonnelPart
,@PersonnelCode
,@Year
,@Month
,@EfficiencePerformanceDay
,@EfficiencePerformanceDayInsurance
,NULL
,(CAST((@OverTime / 60)AS VARCHAR(4)) + ':' + CAST((@OverTime % 60)AS VARCHAR(4)))
,@Mission
,(CASE WHEN @LeaveMinute = 0
THEN CAST(@Leave AS VARCHAR(4))
ELSE CAST(@Leave AS VARCHAR(4)) + '/' + CAST((@LeaveMinute / 60) AS VARCHAR(4)) +
':' + CAST((@LeaveMinute % 60)AS VARCHAR(4))
END)
,(CASE WHEN @LeaveUnPaidMinute = 0
THEN CAST(@LeaveUnPaid AS VARCHAR(4))
ELSE CAST(@LeaveUnPaid AS VARCHAR(4)) + '/' + CAST((@LeaveUnPaidMinute / 60) AS VARCHAR(4)) +
':' + CAST((@LeaveUnPaidMinute % 60)AS VARCHAR(4))
END)
,@LeaveSick
,@Absent
,@EmployTypeName
,@WorkHouseName
,@PartCaption
,@PFName
,@PLName
,@OrganizationPartCaption
,@UserCode
,@WorkHouseCode)
END
BEGIN TRY
CLOSE GetLeave
DEALLOCATE GetLeave
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
CLOSE GetMission
DEALLOCATE GetMission
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
CLOSE GetOverTime
DEALLOCATE GetOverTime
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
CLOSE GetOverTimeInHolidays
DEALLOCATE GetOverTimeInHolidays
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
DROP TABLE #tOnline
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
DROP TABLE #tLeave
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
DROP TABLE #tMission
END TRY
BEGIN CATCH
END CATCH
DELETE TOP(1) FROM #tPersonnel
END
DROP TABLE #tPersonnelI've add begin and commit and rollback transaction. but it's not fired when error occured.
2条答案
按热度按时间0x6upsns1#
You can use a
TRY...CATCHblock to catch errors and rollback the transaction when an error occurs in the main or any inner stored procedure.xmq68pz92#
One of the option is, let the inner procedure throw error and you can catch or handle the error using TRY..CATCH in the main stored procedure. I am seeing you have applied TRY..CATCH only for closing cursor and dropping temp table. But any error occurs on these parts will get suppressed as no code in the CATCH block. You don't need to put TRY..CATCH for every statements separately. The following template will help you.
In the above code, any unhandled errors from the inner procedures will immediately jump to CATCH block in the main procedure. Transaction will be rolled back. Reading actual error message using ERROR_MESSAGE() function, added additional message just to give more clarity to caller and re-throwed the error. It is up to you whether error can printed or re-throwed in the main procedure.