错误 从嵌套的 MSSQL 过程传递到父过程

Question

我有一个父存储过程和一个嵌套存储过程。父存储过程循环调用嵌套存储过程。现在，当存在某些条件匹配时，我提出了错误。当引发此错误时，我想停止所有进程并返回错误。

CREATE Proc [dbo].[Usp_GenSalarySheet](@SalData [HRM].UTDT_SalaryData ReadOnly)
AS
    set nocount on
    DECLARE @SMT NVARCHAR(MAX)
    SET @SMT = 'Create Table ##SalarySheet (StaffID INT,FullName NVARCHAR(1024),PresentDays NVARCHAR(1024),Absent NVARCHAR(1024),Department NVARCHAR(1024),Designation NVARCHAR(1024),'
    DECLARE @HopName nvarchar(max)
    Declare HOPCursor cursor for select ID from HRM.tbl_HOP 
    OPEN HOPCursor
    FETCH NEXT FROM HOPCursor INTO @HopName
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @SMT = @SMT + ' [' +  @HopName + '] DECIMAL(19,7),'
            FETCH NEXT FROM HOPCursor into @HopName
        END
        SET @SMT = @SMT + '[Total] DECIMAL(19,7))'
    CLOSE HOPCursor
    DEALLOCATE HOPCursor
    print (@smt)
    exec (@SMT)
    select * into #temp  from @SalData
    Declare @TopID INT
    While (Select Count(*) From #Temp) > 0
    Begin
        Select Top 1 @TopID = StaffID From #temp
        Declare @StaffID INT =(select top 1 StaffID from #temp)
        Declare @StaffName NVARCHAR(1024) = (SELECT TOP 1 FullName FROM #temp)
        Declare @WorkingDays Int = (SELECT top 1 WorkingDays from #temp)
        Declare @Leave INT = (SELECT top 1 [Absent] from #temp)
        INSERT INTO ##SalarySheet(StaffID,FullName,[Absent]) values(@StaffID,@StaffName,@Leave)
        DECLARE @HOPType INT
        DECLARE @Value Decimal(19,7)
        DECLARE @CalcVal DECIMAL(19,7) = 0
        DECLARE @Formula NVARCHAR(MAX)
        DECLARE @Total DECIMAL(19,7)
        DECLARE @PayEvery INT
        DECLARE @Round Int
        Declare HOPList Cursor for SELECT ID,HOPType,Value,Formula,RoundOff,PayEvery FROM HRM.Tbl_HOP order by Schedule
        open HOPList
            FETCH NEXT FROM HOPList INTO @HopName,@HOPType,@Value,@Formula,@Round,@PayEvery
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    if exists(select * from HRM.[Tbl_ContractHOPDetails] where PersonalDetailsID = @StaffID and HOPID = @HopName)
                    print('select * from HRM.[Tbl_ContractHOPDetails] where PersonalDetailsID = ' + convert(varchar(max), @StaffID) + ' and HOPID =' + convert(varchar(max),@HopName))
                        begin
                        if(@HOPType=51)
                            begin
                                exec HRM.Usp_GetSalaryValueFromFormula @StaffID,@Formula,@HOPType,@Leave,@WorkingDays,@Value output
                            set @HOPType= 50
                            end
                        if(@HOPType=50)
                            begin
                                set @CalcVal = @value
                            END
                        IF(@HOPType=38)
                            BEGIN
                                SET @CalcVal = @Value - ((@Value/@WorkingDays) * @Leave)
                            END
                            if(@PayEvery= 40)
                                begin
                                    set @CalcVal = ((@CalcVal * @WorkingDays) - (@CalcVal * @Leave))
                                end
                            if(@Round = 45)
                                begin
                                    set @CalcVal = round(@CalcVal,2)
                                end
                            else if(@Round = 46)
                                begin 
                                    set @CalcVal = CEILING(@CalcVal)
                                end
                            else if(@Round = 47) 
                                begin
                                    set @CalcVal = FLOOR(@CalcVal)
                                end
                        set @SMT ='UPDATE ##SalarySheet SET [' + @HopName + '] = ' + cast(@CalcVal as nvarchar(max)) + ' where StaffID = ' + cast(@StaffID as nvarchar(max))
                    exec (@smt)
                    end
                    SET @CalcVal = 0
                FETCH NEXT FROM HOPList INTO @HopName,@HOPType,@Value,@Formula,@Round,@PayEvery
                END
        close HOPList
        DEALLOCATE HOPList
            set @SMT ='UPDATE ##SalarySheet SET [Total] = ' + cast(@Total as nvarchar(max)) + ' where StaffID = ' + cast(@StaffID as nvarchar(max))
            exec (@smt)
    Delete #temp Where StaffID = @TopID
    end
select * from ##SalarySheet
drop table ##SalarySheet

这是我的父存储程序，嵌套过程如下：

CREATE proc [HRM].[Usp_GetSalaryValueFromFormula](@StaffID INT,@val nvarchar(max),@HOPType INT,@Leave INT,@WorkingDays INT, @GetResult Decimal(19,7) output)
as 
set nocount on
Declare @Formula Varchar(max)
declare @initial INT =0
declare @final INT =0
Declare @DataVal NVARCHAR(MAX) -- set the value from HOP table 
declare @FieldVal nvarchar(max)
declare @cnt int = 0
Declare @Complete Int =CHARINDEX ('[',@val,0)
while (@Complete <> 0)
begin
    set @initial = CHARINDEX ('[',@val,0)
    set @final = CHARINDEX(']',@val,0)
    set @FieldVal = SUBSTRING(@val,@initial,(@final-@initial) + 1)
    if len(@FieldVal)<>0
        begin
            select @HOPType = HOPType, @DataVal= ( case when HOPType = 51 then [Formula] else cast([Value] as nvarchar(max)) end)  from HRM.Tbl_ContractHOPDetails where PersonalDetailsID = @StaffID and HOPID in(select ID from HRM.tbl_HOP where HOPName = replace(replace(@fieldVal,'[',''),']',''))
            if (@DataVal is null or @DataVal ='')
                begin
                    RAISERROR ('Nested HOP is not defined.',11,1) 
                    RETURN
                end
            print(@DataVal)
            if ISNUMERIC(@DataVal)=1
                begin
                    if(@HOPType = 38)
                        begin
                            SET @DataVal = cast(@DataVal as decimal(19,7)) - ((cast(@DataVal as decimal(19,7))/@WorkingDays) * @Leave)
                        end
                end
            set @val = replace(@val,@fieldVal,@DataVal)
            set @fieldVal= ''
            set @DataVal = ''
        end
    set @Complete = CHARINDEX ('[',@val,0)
    set @fieldVal =''
    set @final =0
    set @initial = 0
end
SET @Complete =CHARINDEX ('{',@val,0)
while (@Complete <> 0)
    BEGIN
        set @initial = CHARINDEX ('{',@val,0)
        set @final = CHARINDEX('}',@val,0)
        set @FieldVal = SUBSTRING(@val,@initial+1,(@final-@initial)-1)
        if len(@FieldVal)<>0
            begin
                set @DataVal = isnumeric((SELECT 0 FROM [HRM].Tbl_StaffTag where CValue = @FieldVal and StaffID = @StaffID))
                set @FieldVal = '{' + @FieldVal + '}'
                set @val = replace(@val,@fieldVal,@DataVal)
                set @fieldVal= ''
                set @DataVal = ''
            end
        set @Complete = CHARINDEX ('{',@val,0)
        set @final =0
        set @initial = 0
    END
DECLARE @RetrunVal DECIMAL(19,7)
declare @ParmDefinition Nvarchar(512) = '@GetVal decimal(19,7)  OUTPUT'
Declare @SMT NVARCHAR(MAX) = ' SET @GetVal = ' + @val
EXECUTE sp_executeSQL @Smt, @ParmDefinition, @GetVal =@RetrunVal OUTPUT
set @GetResult = @RetrunVal

但是在当前情况下，它会引发错误，并再次从主程序运行下一步循环。但是我想在这次引发错误后终止整个过程

请帮助我

Answer 1

我的猜测是 RaiseError 将控件扔回调用方，这可能会使 Return 语句无法访问。调用方（循环）继续进行下一次迭代。