Home Forums SQL Server 2008 T-SQL (SS2K8) Finding row number which caused the error in Table value parameter insertion RE: Finding row number which caused the error in Table value parameter insertion

  • As you long as your stored procedure is not part of an outer transaction, it is doable:

    BEGIN TRY

    MERGE ...

    END TRY

    BEGIN CATCH

    DECLARE cur CURSOR STATIC LOCAL FOR

    SELECT .. FROM @tvp

    OPEN cur

    WHILE 1 = 1

    BEGIN

    FETCH cur INTO @var1....

    IF @@fetch_status <> 0

    BREAK

    BEGIN TRY

    MERGE ....

    END TRY

    BEGIN CATCH

    PRINT 'Errors for ' + ....

    END CATCH

    END

    DEALLOCATE cur

    END CATCH

    But if your procedure is part of a greater transaction, this may not work as the error may doom the transaction and you cannot do the fallback.

    And of course it is a burden to maintain two code paths..

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]