October 17, 2001 at 1:29 am
I'm trying to do the following:
1) If there is an error inside a transaction, rollback and return -1
2) If the error is outside the transaction, just return -1
3) Is there anything in SQL similar to On Error goto errh:
4) Ideally I would like to store the error details in log table.
Could you please review the following code.
--**********************************
Use Northwind
Create PROCEDURE u_errorTest AS
DECLARE @intErrorint
BEGIN
SELECT * FROM CUSTOMERSzzzzzzzzzzzzz
SET @intError = @@ERROR
IF @intError <>0 GOTO Errh
BEGIN TRANSACTION SelectCustomers
--SELECT * FROM CUSTOMERS
SELECT * FROM CUSTOMERSzzzzzzzzzzzzz
SET @intError = @@ERROR
IF @intError <>0 GOTO Errh
COMMIT TRANSACTION SelectCustomers
Return 1
Errh:
IF @@TRANCOUNT>0
ROLLBACK TRANSACTION SelectCustomers
INSERT INTO ERRORLOG(@@ERROR,GETDATE())
Return -1
END
October 17, 2001 at 8:26 am
I dont think you want to use -1, I think(someone confirm?) that SQL reserves -1 through -15 for its own use. Nothing quite like on error goto. Basically you have to check @@error after each line, then branch to a label and execute your logging/set your return variable/etc.
Andy
October 25, 2001 at 7:15 pm
You could have
Create PROCEDURE u_errorTest AS
DECLARE @intError int
SELECT * FROM CUSTOMERSzzzzzzzzzzzzz
SET @intError = @@ERROR
IF @intError <>0 GOTO Errh
BEGIN TRANSACTION
SELECT * FROM CUSTOMERSzzzzzzzzzzzzz
SET @intError = @@ERROR
IF @intError <>0 GOTO TxErrh
COMMIT TRANSACTION
Return
TxErrh:
ROLLBACK TRANSACTION
Errh:
INSERT INTO ERRORLOG(@@ERROR,GETDATE())
Return 1
go
I never like to name transactions as someone might think they can nest them and roll back bits - always a problem to control.
If you want to use @@TRANCOUNT then you should save the value on entry to the sp and compare to that - just in case someone runs the SP inside a transaction.
If you want to log errors like this it is probably better to call an sp to do it then you can change your log table in one place (you will only need to add single entries so not a problem with encapsulating the table) - I would add the spid too.
0 is usually used as a successful return code - you could use return @intError.
Cursors never.
DTS - only when needed and never to control.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply