Stored Procedure Errors 2

[Hpatel]

I am having trouble finding where did the error occured in the prodecure. I modifying a previously written procedure which inserts and updates. But if there is error at insert block it make @@error=1 and if there is no error then @@error=0. Now in the next block of the code, if there is error then @error=1 but this doesnt tell me that if there was errors in the previous blocks of code. I am trying to find a solution that would let me know of the all the errors in the stored procedure and which block of the procedure. I would appreciate you help. If you have any suggestion please let me know. Thank you.

 

[tlbroadbent]

Please show us the current code so we can make recommendation for modification. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Andel]

One thing you can do is to execute the stored procedure block by block if you can. Another thing is to give a different error message for each block using raiserror command. See RAISERROR in BOL.


Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[hirick]

@@error is the value returned by the system from the previous statement. You don't set it. Also, as soon as you use it (for instance, if @@error <> 0), it will be reset to zero.
In your procedure, try this:
Declare @error int
Declare @errmsg varchar(200)
Set @errmsg=''
...
Update myTable Set SomeField=5000
Set @error=@@error
If @error <> 0
Begin
set @errmsg=@errmsg + ' ' +
Convert(varchar,@error)+ ': updating MyTable'
End
...
(repeat the error test after every statement that could cause an error)
...
If len(@errmsg)>0
Begin
Select @errmsg -- return the message, and/or
Raiserror (@errmsg,16,1) -- raise an error
End

 

[Hpatel]

Thank you guys for you help. I am going to try this out.