WARNINGS in Stored Procedures 1

[itsmarkdavies]

I have added stages to my Stored Procedure which call another Stored Procedure at various points to say whether the particular stage has been successful. The Stored Procedure that does the logging just writes TimeStamped text into an Audit Table, e.g. after a major insert in my main Stored Procedure :-

exec LogProgress 'First Insert in sp1 completed', getdate

The various steps in the Stored Procedure are logged OK but the problem arises when there is an error. The Stored Procedure seems to keep running through and the logging procedure keeps logging, despite the fact that an error occurred way up in the procedure.

I would expect a Stored Procedure to stop execution when it hits an error. is this not the case ? !

I have heard about using "SET WARNINGS OFF" but do not understand the implications of this. Can anyone advise me ?. Thanks.


itsmarkdavies@hotmail.com

 

[billchris]

For most errors stored procedures don't stop. You have to check for the error yourself.

Something like this will work:

  if @@ERROR <> 0 
  begin
    exec LogProgress 'First Insert in sp1 failed', getdate
    return (<put an error code here if you like>)
  end

  exec LogProgress 'First Insert in sp1 completed', getdate

@@ERROR returns the error number for the last Transact-SQL statement executed, so you have to check it after every statement that might fail.

If you are in a transaction you need to make sure you do a rollback before you return, but that will also rollback all of the logging done in the transaction.

“I have always found that plans are useless, but planning is indispensable.” --Dwight Eisenhower

 

[Kris11]

Mark,

About first question, how are you handling the error situation, are you raising an error or just trying to return back if there is an error?

And second question, I am guessing that you are talking about SET ANSI_WARNINGS OFF, if that is the case it is just a way to suppress some of the warnings that you might get in following conditions

1. Trying to insert a long string to a much smaller database field.
2. Trying to do an aggregate functions ( MIN, MAX, AVG etc.) on a field that you know can have NULL values etc. etc.

So in some of these above cases you might not want the store procedure to throw an exception and stop the execution, thatz where we use SET ANSI_WARNINGS OFF. By default it is always ON.