sql-server - SQL Server try catch inside a while loop statement..异常后循环会正常继续吗？

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

这不是确切的代码，但我已经削减了这个问题所需的最低代码。 try block 中的 INSERT 语句有时可能会导致主键冲突。

我不希望循环因违反主键而终止。相反，我希望它在不插入违规的特定行的情况下继续。

这是正确的做法吗？

Answer 1

一个简单的测试就可以证明这一点..这个循环将运行到无穷大。

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

有一些errors这将像下面这样打破循环..

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.