Jump to content

@@ERROR or @@ROWCOUNT or TRY CATCH ?


davej

Recommended Posts

What is the best way to keep track of what has happened inside a trigger or procedure before executing a COMMIT ? I am thinking in terms of SQL Server. Thanks.

Link to comment
Share on other sites

The experimenting I have done so far seems to indicate that an approach like this;BEGIN TRANSACTIONDECLARE @ROWCNT INTEGERDECLARE @ERRCNT INTEGER...UPDATE EMPLOYEESET Phone = '123-456-7890'WHERE EmpID = 12345;SET @ROWCNT = @ROWCNT + @@ROWCOUNTSET @ERRCNT = @ERRCNT + @@ERROR...Does not work in SQL Server 2008. The second SET statement is affected by the first SET statement.What I am using at the moment is a summing of @@ROWCOUNT inside a TRY-CATCH. That seems to work fairly well.

Link to comment
Share on other sites

not exactly sure what you're trying to keep track of, but given the example above...

SELECT	 @ROWCNT = @ROWCNT + @@ROWCOUNT	,@ERRCNT = @ERRCNT + @@ERROR

... would preserve the @@ERROR while incrementing the @ROWCNTIf you want a count of errors you might need to alter that a little as @@ERROR will have a non 0 error code if an error occured and it appears that you're looking for an error count

SELECT	 @ROWCNT = @ROWCNT + @@ROWCOUNT	,@ERRCNT = @ERRCNT + CASE WHEN @@ERROR != 0 THEN 1 ELSE 0 END

will give you an error count rather than summing the error codes.happy coding

Link to comment
Share on other sites

not exactly sure what you're trying to keep track of, but given the example above...
SELECT	 @ROWCNT = @ROWCNT + @@ROWCOUNT	,@ERRCNT = @ERRCNT + @@ERROR

... would preserve the @@ERROR while incrementing the @ROWCNTIf you want a count of errors you might need to alter that a little as @@ERROR will have a non 0 error code if an error occured and it appears that you're looking for an error count

SELECT	 @ROWCNT = @ROWCNT + @@ROWCOUNT	,@ERRCNT = @ERRCNT + CASE WHEN @@ERROR != 0 THEN 1 ELSE 0 END

will give you an error count rather than summing the error codes.happy coding

Ok, that looks good -- so I put them on one line with a comma. I have never used CASE in SQL. I am just trying to verify that the expected number of rows were indeed affected and that there were no errors before issuing COMMIT. Do you know if I can throw an error to test this? Thanks.
Link to comment
Share on other sites

you can throw a test error with:

RAISERROR 'test error'

CASE is very powerful, I highly recommend becoming familiar with it.if you're outside a try/catch block there's a good chance that any significant error is going to exit your routine, which if you're using explicit transactions can be catastrophicI can't emphasize enough the importance of reading the books on-line topics regarding transactions, try/catch and error handling as well as running some unit tests to see how these piece fit together in order to get a feel for them in practice before using them in a production environment.if you goto books on line and find TRY...CATCH in the Transact SQL reference; examples B & C both illustrate working with transactions in try/catch blocksalso check out the topic "Database Engine Error Severities", some severity levels will only return warnings and not throw an exception in a try/catch, others will trip a try/catch but not pop out of a routine otherwise, some will sever the current connection, the highest severities indicate hardware or software problemsif you have any reservations or confusion about using transactions you're probably better off not using a trans than getting one wrong and ending up with deadlocks... remember that there's always an implicit transaction, if a data modification statement fails nothing is changed...

Link to comment
Share on other sites

For now I am using something like this...

DECLARE @RCNT INTEGERBEGIN TRANSACTIONBEGIN TRYUPDATE TABLE1SET NAME = @nameWHERE EmpID = @EmpIDSET @RCNT = @@ROWCOUNT -- should be 1UPDATE TABLE2SET DEPT = @deptnameWHERE EmpID = @EmpIDSET @RCNT = @RCNT + @@ROWCOUNT -- should be 2UPDATE TABLE3SET MANAGER = @supernameWHERE EmpID = @EmpIDSET @RCNT = @RCNT + @@ROWCOUNT -- should be 3IF (@RCNT = 3)BEGIN  COMMIT TRANSACTION  PRINT 'SUCCESS'ENDELSEBEGIN   ROLLBACK TRANSACTION   PRINT 'ERROR OCCURRED -- UNEXPECTED ROWCOUNT' ENDEND TRYBEGIN CATCH   ROLLBACK TRANSACTION   PRINT 'DATABASE ERROR OCCURRED' END CATCH

Link to comment
Share on other sites

looks good. tried testing by raising some errors in there and also by passing bad data?might be able to simplify, if you know you want to bail out if any of those statements fail to affect a record you can omit the count and the following statements by raising your own exceptionI didn't test this, so I might have a syntax error in here, bear with me

--DECLARE @RCNT INTEGERBEGIN TRANSACTIONBEGIN TRYUPDATE TABLE1SET NAME = @nameWHERE EmpID = @EmpID--SET @RCNT = @@ROWCOUNT -- should be 1IF @@ROWCOUNT != 1	RAISERROR 'UNEXPECTED ROWCOUNT'UPDATE TABLE2SET DEPT = @deptnameWHERE EmpID = @EmpID--SET @RCNT = @RCNT + @@ROWCOUNT -- should be 2IF @@ROWCOUNT != 1	RAISERROR 'UNEXPECTED ROWCOUNT'UPDATE TABLE3SET MANAGER = @supernameWHERE EmpID = @EmpID--SET @RCNT = @RCNT + @@ROWCOUNT -- should be 3IF @@ROWCOUNT != 1	RAISERROR 'UNEXPECTED ROWCOUNT'/*IF (@RCNT = 3)BEGIN  COMMIT TRANSACTION  PRINT 'SUCCESS'ENDELSEBEGIN   ROLLBACK TRANSACTION   PRINT 'ERROR OCCURRED -- UNEXPECTED ROWCOUNT' END*/END TRYBEGIN CATCH   ROLLBACK TRANSACTION      IF ERROR_NUMBER() = 50000	   PRINT 'ERROR OCCURRED -- UNEXPECTED ROWCOUNT';   ELSE	   PRINT 'DATABASE ERROR OCCURRED'; END CATCH

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...