October 9, 2003 at 11:47 pm
In a stored procedure, between the transaction block, there is a possibility of encountering an error. Once the error is encountered the execution of the procedure is halted and the following error is thrown.
"Server: Msg 266, Level 16, State 1, Procedure spDeleteKCCategory, Line 28
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1."
Is there a way to Rollback transaction on error, something like exception handling in TSQL? Please advice.
Thanx in advace,
Vijay
October 10, 2003 at 1:03 pm
You should be checking for errors after any insert, update or deletes and responding to them within you proc (rolling back the transaction) and responding to the calling procedure or application.
You could be checking for error like this
SELECT @errorcode = @@ERROR;
then responding
IF @errorcode <> 0
BEGIN
RETURN -1
END
If you Return before you Commit then you have essentially rolled back.
Hope that helped.
-kel
K Leb
October 10, 2003 at 5:27 pm
I do this all the time. As kleb_writes said you want to check for errors after ANYTHING that might cause an error. As a note you also want to check for errors BEFORE creating them. IE: if you try to put a dup value in a primary key you want to raise the error before you even try. I usually use something like the following...
declarations and code go here...
Update....
SELECT @iErr = @@Error, @NumRows = @@ROWCOUNT
IF @iErr != 0 GOTO ErrHandler
SET @vMsg = CONVERT(varchar,GetDate()) + ':' + 'Updated ' + convert(varchar,@NumRows) + ' records in table foo'
PRINT @vMsg
put more code here...
-------------------------------------------------------------------------------
ErrHandler:
-------------------------------------------------------------------------------
IF (@iErr != 0)
BEGIN
SET @vMsg = CONVERT(varchar,GetDate()) + ':' + 'Error.'
ROLLBACK TRANSACTION
PRINT @vMsg
GOTO BAIL
END
ELSE
BEGIN
SET @vMsg = CONVERT(varchar,GetDate()) + ':' + 'Finished without errors.'
PRINT @vMsg
COMMIT TRANSACTION
GOTO BAIL
END
-------------------------------------------------------------------------------
BAIL:
-------------------------------------------------------------------------------
RETURN
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Edited by - gljjr on 10/10/2003 5:34:51 PM
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply