SQL Server try catch inside a while loop statement.. will loop continue normally after exception?

Question

WHILE(@InitialLoopValue <= @FinalLoopValue)
BEGIN  
    BEGIN TRY
        INSERT INTO Table(GCMRegId, Title, Url, OSType, NotificationType, IMEICode)     
            SELECT 
                GCMRegId, @Title, @Url, SourceId, SubsMasterId, IMEICode 
            FROM 
                #EligibleForNotification WITH(NOLOCK) 
            WHERE
                Id = @InitialLoopValue                
    END TRY
    BEGIN CATCH

    END CATCH   

    SET @InitialLoopValue  = @InitialLoopValue  + 1     
END

This is not the exact code but I have cut the minimum required code for this question. The INSERT statement inside the try block may sometimes cause a primary key violation.

I don't want loop to terminate by primary key violation. Instead I want it to continue without insert of particular row that is giving violation.

Is it correct way of doing this?

Answer 1

A simple test would prove this..This loop will run to infinity.

create table #test
(
id int not null primary key
)



declare @n int=1
while @n<10
begin
begin try
insert into #test
select @n
end try

begin catch
select ERROR_MESSAGE();
end catch
select @n=@n

end

There are some errors which will break the loop like below..

TRY…CATCH constructs do not trap the following conditions:

1. Warnings or informational messages that have a severity of 10 or lower.

2. Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.

3.Attentions, such as client-interrupt requests or broken client connections.

4. When the session is ended by a system administrator by using the KILL statement.