May 12, 2004 at 1:00 pm
Given this:
----------------
SET XACT_ABORT ON
begin tran
update...
update...
commit tran
return 1
----------------
What would be the return value of the sp if an error was encountered?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
May 13, 2004 at 2:57 am
This is a trick question: If you use "SET XACT_ABORT ON" the batch will be aborted in case of an error, therefore there isn't any return value to be checked. What I mean is:
If you run the procedure from another procedure (or from a simple T-SQL batch), like this:
DECLARE @ret int
EXEC @ret=Test
PRINT @ret
then the PRINT statement will not be executed in case of an error.
If you run the procedure from Visual Basic or another language, using a ADODB.Command object, you will get an runtime error. If you ignore the error and look for the value for Parameters(0), which normally holds the return value, it will be Empty. You should check Err.Description to see the error message or cmd.ActiveConnection.Errors(0).NativeError if you want the error code of the original error.
Razvan
PS. For an excellent article written by Erland Sommarskog (SQL Server MVP) about error handling in stored procedures go to:
http://www.algonet.se/~sommar/error-handling-II.html#XACT_ABORT
May 13, 2004 at 9:29 am
Thanks, cmd.ActiveConnection.Errors(0).NativeError is going to help.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply