Continue execution without throw the exception

Question

I running a procedure in while loop and when there is an exception its exiting the procedure. But I want the procedure should continue executing other entity in while loop and should exit the procedure instead it would log error in the table. So that we can the table once the procedure completed the execution. Please let me know if you any solution.

 CREATE PROC [DYNAMIC_PROC_MAIN]  AS
BEGIN

SET NOCOUNT ON

/* Variable Declaration */
DECLARE @CheckError INT = 0;
DECLARE @ErrorMessage VARCHAR(4000); 
DECLARE @Current_date datetime
DECLARE @VMaxlevel bigint    
DECLARE @RowCount bigint  

DECLARE @Vraa_name VARCHAR(50)
DECLARE @Vformula VARCHAR(50)
DECLARE @Vlevel INT
--DECLARE @Vmonth VARCHAR(50) = '202306'

DECLARE @InputValue varchar(1000)
DECLARE @LastRefreshDate datetime

DECLARE @number_of_rows int 
DECLARE @row_count INT

DECLARE @Counter INT
SET @Counter = 1

DECLARE @CounterList INT
SET @CounterList = 1

DECLARE @Vtemp VARCHAR(4000)

SET @VMaxlevel = 
    (select max(level_id)
FROM Level_t)

WHILE (@Counter <= @VMaxlevel )


BEGIN TRY
        --Print(@VMaxlevel)
        Set @InputValue = @MonthValue

    SELECT @Current_date=GETUTCDATE()
    SELECT @LastRefreshDate=GETUTCDATE()
    
    
        --Print(@Counter)
      
        SET @RowCount = 
    (SELECT count(distinct lt.ID) FROM Level_t2 lt
WHERE lt.level=@counter)
         
        WHILE (@CounterList <= @RowCount )
     BEGIN
        

     /* Begin creation a temp table to store recrods in runtime */
            BEGIN
            IF OBJECT_ID('tempdb..#kpi_maintmptbl') IS NOT NULL
            BEGIN
            --print 'Hello'
            DROP TABLE #kpi_maintmptbl
            END
            --GO
            create table #kpi_maintmptbl
            with (
            DISTRIBUTION = ROUND_ROBIN
            ,HEAP
            )
            AS (select distinct lt.name FROM Level_t3 lt
            where lt.level=@CounterList)
            

            END

            /* End creation a temp table to store recrods in runtime */

            DECLARE @recordCount int = (SELECT COUNT(1) from #kpi_maintmptbl)
            DECLARE @counter1 int = 1
            DECLARE @name VARCHAR(50) -- database name 
            WHILE @counter1 <= @recordCount 

            BEGIN

                  SET @name = (SELECT [name]
                  FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY                     [raa_name]) AS RN
                  FROM #kpi_maintmptbl) as T
                  WHERE RN = @counter1)
                 

                              
                  PRINT @name
                  PRINT @Vlevel
                  Print @counter1

                
                EXEC DYNAMIC_PROC  @name
                 
                 
                  SET @counter1 = @counter1 + 1

             END
     SET @CounterList = @CounterList +1
    END
--SET @Counter = @Counter +1


END TRY

Begin Catch

    DECLARE
        @ErrorSeverity INT,
        @ErrorState INT;
                

            SELECT @ErrorMessage = ERROR_MESSAGE(),          
            @ErrorSeverity = ERROR_SEVERITY(),          
            @ErrorState = ERROR_STATE();
            RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    
        
        SELECT @Current_date=GETUTCDATE()
       

        SET @CheckError = ERROR_NUMBER()
        SET @ErrorMessage = concat(@name,'-',ERROR_MESSAGE())
        SELECT @Current_date=GETUTCDATE()

       INSERT INTO dl_config.CL_Error_Log 
       VALUES('DYNAMIC_PROC_MAIN',@CheckError,@ErrorMessage,@Current_date,NULL);

   --THROW

   End Catch

SET @Counter = @Counter +1      

    End
    
GO

Answer 1

If you want your procedure to continue on error, you shouldn't throw after you catch it (or use raiseerror). Instead, you can use a table, a table variable or a temporary table to store error information and continue the loop.

Here's an example:

DECLARE @Errors as Table
(
    Severity int,
    State int,
    Message nvarchar(4000)
);

WHILE (@Counter <= @VMaxlevel )
BEGIN TRY
    EXEC DYNAMIC_PROC
END TRY
BEGIN CATCH
    INSERT INTO @Errors(Severity, State, Message)
    VALUES(ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE());
END CATCH
-- This will ensure the counter will be raised even in case of an error
SET @Counter = @Counter +1
END

As a side note - loops are usually the wrong tool to use in SQL, so if it possible to avoid the loop altogether, it would probably be a better thing to do.