0

In the below query I want to catch the constraint errors and in the same time I want do not want to abort the transaction .

But the below query is aborting the transaction once a single constraint error comes in.

When I use the xact_abort off without try block the transaction is not aborting 

create table test_const
(
name varchar(300),
id int primary key 
) 

create table erro_log
(
error nvarchar(max),
error_desc nvarchar(max),

)

set xact_abort off

begin try



    insert into test_const select  'FRO',1
    insert into test_const select  'FRO',1
    insert into test_const select  'FRO',4
    insert into test_const select  'FRO',5



 end try


begin catch 


                declare @error varchar(500)
                select @error = @@error
                INSERT INTO erro_log 
                    (
                    Error,
                    Error_desc
                    )

                VALUES (
                     @error
                    ,error_message()

                    )

end catch   

select * from erro_log 
select * from test_const
gotqn
  • 42,737
  • 46
  • 157
  • 243
Froze Khan
  • 1
  • You may want to look at `IGNORE_DUP_KEY` index option. http://stackoverflow.com/q/2594193/4116017 – Vladimir Baranov May 03 '17 at 06:31
  • I am working this for a product using Agile methodology.I am not supposed to alter the constraint of the tables . – Froze Khan May 03 '17 at 06:40
  • There is no transaction here, unless you started one before this code is happening. Do you mean you want to execute the other `INSERT` statements even if one of them fails? – Jeroen Mostert May 03 '17 at 09:34
  • Furthermore (quite important), will there *be* a transaction in the final version that is supposed to ensure all of these statements happen, or none of them do? (You see how that could be problematic.) Last but certainly not least, I doubt this is *actual* code, since there is no point to having `INSERT` statements with values that are known to conflict. Will the actual client code use multiple `INSERT` statements in a single batch, or a series of `INSERT` statements in separate batches? Error handling in T-SQL is a very complicated subject, so we need to be precise about these things. – Jeroen Mostert May 03 '17 at 09:46
  • we have a prouduct where single currency type is made as primary key . – Froze Khan May 03 '17 at 13:21
  • 1
    It does not help you to refer to a column in your actual table without providing the schema of that table nor how it is used nor your goal in this process. You can refer to erland's lengthy discussion of error handling http://www.sommarskog.se/error_handling/Part1.html but you will find that tsql is not particularly "good" at this. And don't forget that there are many possible errors beyond just this particular one that may doom anything that you attempt after the error. – SMor May 03 '17 at 14:06

0 Answers0