SQL Server On raising error cancel all sub stored procedures inside of the main stored procedure [duplicate]

e4eetjau  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(113)

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]                   
END

The 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
END

And 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 #tPersonnel

I've add begin and commit and rollback transaction. but it's not fired when error occured.

sql-server

2条答案

按热度按时间
0x6upsns

0x6upsns1#

You can use a TRY...CATCH block to catch errors and rollback the transaction when an error occurs in the main or any inner stored procedure.

CREATE PROCEDURE [Csm].[ReceptionSelectViews]
    @IsRead bit,
    @Years nvarchar(4)
AS
BEGIN
    BEGIN TRY
        -- Start a transaction in the main procedure
        BEGIN TRANSACTION;

        -- Execute the inner procedures
        EXECUTE [Csm].[ReceptionSelectViews1];
        EXECUTE [Csm].[ReceptionSelectViews2];
        EXECUTE [Csm].[ReceptionSelectViews3];

        -- If no errors occurred, commit the transaction
        COMMIT;
    END TRY
    BEGIN CATCH
        -- If an error occurs, rollback the transaction
        ROLLBACK;

        -- Optionally, you can log or handle the error here
        -- You can use ERROR_MESSAGE(), ERROR_NUMBER(), etc., to get error details

        -- Re-throw the error to propagate it up the call stack
        THROW;
    END CATCH;
END
赞(0）分享  回复(0）举报  2023-10-15
xmq68pz9

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.

CREATE PROCEDURE [Csm].[ReceptionSelectViews]
@IsRead bit ,@Years nvarchar(4)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

        EXECUTE [Csm].[ReceptionSelectViews1] 
        EXECUTE [Csm].[ReceptionSelectViews2] 
        EXECUTE [Csm].[ReceptionSelectViews3]  

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF(@@TRANCOUNT > 0)  
        BEGIN  
            ROLLBACK TRANSACTION  
        END 
        DECLARE  @Errmsg    VARCHAR(1000)
        SET @Errmsg = 'Error on ReceptionSelectViews: ' + ERROR_MESSAGE()
        RAISERROR (@Errmsg, 15,75) 
    END CATCH
END

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.

赞(0）分享  回复(0）举报  2023-10-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com