Link to home
Create AccountLog in
Avatar of quiTech
quiTechFlag for Canada

asked on

How to clear @@error or general error flag in T-SQL


I have a script that inserts some data into a table.  Occasionally, source data is duplicated in error, causing an insert to fail and setting the @@error flag.

Is there a way I can clear this error so that when the procedure finishes, it doesn't think there were any errors?

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

@@error is cleared on each and every next statement, so unless you "save" the value of @@error in the next statement (of the one that fails), the script won't know anyting at the end.
note: some errors will make the script simply end, depending on the error severity.
you need to comment that  error validation statement that;s one of the easiest way..
Avatar of quiTech


I figured @@error gets cleared with each statement.  But something is keeping track of there being an error.

The insert statement returns this error:
Msg 2627, Level 14, State 1, Procedure myprocedure_sp, Line 426
Violation of PRIMARY KEY constraint 'PK_mytable_tb'. Cannot insert duplicate key in object 'dbo.mytable_tb'.
The statement has been terminated.

The procedure merrily continues to process after this without further errors.  But when it's done, it says "Compeleted with errors".  Also, when it runs as a scheduled job, the job reports that it failed.

It's that part that I'm trying to clear.  Sorry I should have been more specific in my original post.
>The statement has been terminated.

means, the full batch has been terminated, and NOT continued.
the severity of the "duplicate key" error is so high that it will terminate the batch, you won't get to the @@error lines below that.

Avatar of quiTech


but... the stored procedure continues to run after this point.  Am I missing something?
>but... the stored procedure continues to run after this point.  
it is not. unless I miss something from my day-to-day practise.

Unless you are doing those inside the trasaction it will continue to execute till tht point, so you have to encapsulate the whole logic inside a Transaction
It will only run further if the error is generated by a dynamic sql execution.
Avatar of quiTech


It definitely keeps running.  The procedure reads individual records from a file and inserts them into a database table (depending on the record type).  It basically looks like this

while not @eof = 'T'
  insert into mytable_tb (columnlist) values (valuelist);
  if @@rowcount <> 1 print 'Error inserting ' + @record
  -- stmts to read next @record from file

When the insert fails, with the above error, the print statement executes, and the procedure continues inserting records from the file.  Sometimes there are multiple duplicate records, and it dispays them all.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of quiTech


I may have to do that... I was hoping to avoid the extra IO involved with that.
yes, duplicate primary key only aborts the statement not the batch.
>I was hoping to avoid the extra IO involved with that.
well, what do you think the INSERT statement does when checking for the primary key, ie why you get the error.
the indexed values is looked up anyhow, so checking for it will not cost you anything more.
What do you mean by:

-- stmts to read next @record from file

how do you read those values. Is it from a file or from a table?
Avatar of quiTech


Angellll: Tried your statement.  Works great, avoids the error, and has no noticable performance hits.  I thought that maybe SQL server would do a more efficient internal check.  But that doesn't seem to be the case.
Avatar of lessthan2

you guys still didn't answer the question.  what is the sql command to clear the error?   something like the error.clear command.
On SQL 2005, you can employ TRY and CATCH to gain manual control of error handling.
Try this

   select 1/0
   select 'you can not divide by zero'
There is no command to clear an error
all you can do is a try catch and the next successfull command clears the error