在循环 SQL 查询中捕获多个错误 [英] Catching multiple errors in loop SQL query

问题描述

我有下面的插入查询，它从 OriginalData 表中选择记录，其中一切都是数据类型 nvarchar(max) 并将其插入到具有特定列的临时表中定义，即 MainAccount 是 INT 类型.

I have the below insert query which selects records from the OriginalData table where everything is of datatype nvarchar(max) and inserts it into the temp table which has specific column definitions i.e MainAccount is of type INT.

我正在逐行插入，因为如果 OriginalData 表中有一条记录，其中 MainAccount 值为Test"，它显然会导致转换错误并且插入将失败.begin try 块用于更新包含错误的表.

I am doing a row by row insert because if there is a record in OriginalData table where the MainAccount value is 'Test' the it will obviously cause a conversion error and the insert will fail. The begin try block is used to update the table with the error.

但是，如果同一行有多个错误，我希望能够同时捕获它们，而不仅仅是第一个.

However if there are multiple errors on the same row I want to be able to capture them both and not just the first one.

TRUNCATE TABLE [Temp] 

DECLARE @RowId INT, @MaxRowId INT  

SET @RowId = 1

SELECT @MaxRowId = MAX(RowId) 
FROM [Staging].[FactFinancialsCoded_Abbas_InitialValidationTest]

WHILE(@RowId <= @MaxRowId)
BEGIN
BEGIN TRY  
    INSERT INTO [Temp] (ExtractSource, MainAccount, 
                        RecordLevel1Code, RecordLevel2Code, RecordTypeNo, 
                        TransDate, Amount, PeriodCode, CompanyCode)
        SELECT 
            ExtractSource, MainAccount,
            RecordLevel1Code, RecordLevel2Code, RecordTypeNo,
            TransDate, Amount, PeriodCode, DataAreaId
        FROM 
            [Staging].[FactFinancialsCoded_Abbas_InitialValidationTest]  
        WHERE 
            RowId = @RowId;

    PRINT @RowId;

END TRY  
BEGIN CATCH  

Update [Staging].[FactFinancialsCoded_Abbas_InitialValidationTest]  
Set ValidationErrors = ERROR_MESSAGE()
where RowId = @RowId  

END CATCH 
SET @RowId += 1;      
END

推荐答案

我没有这样做，而是通过在要转换为非字符串列的每一列上使用 TRY_PARSE() 或 TRY_CONVERT() 来处理此问题.

Instead of doing it this way, I handle this by using TRY_PARSE() or TRY_CONVERT() on each column that I am converting to a non-string column.

如果您随后需要将验证失败存储在另一个表中，您可以再次获取源表中具有非空值且目标表中具有空值的所有行，然后插入这些行进入您的验证失败"表.

If you then need to store the validation failures in another table, you can make a second pass getting all the rows that have a non-null value in the source table and a null value in the destination table, and insert those rows into your "failed validation" table.

这篇关于在循环 SQL 查询中捕获多个错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！