An important item to mention when performing INSERT, UPDATE, and DELETE statements is to ALWAYS check for errors as in:
create procedure usp_reset_to_zero as
DECLARE @err integer
update totalvalues
set total1 = 0,
total2 = 0,
total3 = 0
SET @err = @@error
IF @@err <> 0
BEGIN
RAISERROR ('Unable to UPDATE table totalvalues due to database error number: %d',10,1,@err)
RETURN 1
END
RETURN 0
And make sure your front-end app responds to the return values (that aren't 0 in this case). You can then have your application (via ADO (rollbacktrans), MTS (setabort), etc.) ROLLBACK the transaction if you get the error reported to you in the proc.
If everything works, then COMMIT the transaction in ADO (committrans), MTS (setcomplete)...
If only SQL Server is calling the proc and no front-end will call it, then put your transaction management functionality in the proc via ROLLBACK TRANSACTION and COMMIT calls...
Tom
