Help: Prob handling SQL error in stored proc

[Bill S.]

Hi,

I a stored procedure that inserts a record into a table as
below.

The insert works OK, but if the insert violates a unique
indewx constraint on one of the columns, the proc terminates
immediately, and does NOT execute the 'if @@ERROR <> 0'
statement.

Am I doing something wrong, or do I need to set an attribute
somewhere?

tia,
Bill

begin tran

insert into Users
(UserName, UserPWD, Lname, Fname, UserDesc)
values (@userName, @userPWD, @lname, @fname, @userDesc)

if @@ERROR <> 0
begin
rollback tran
set @returnCode = -2
set @errMsg = 'SQL error '
+ convert(varchar(6), @@ERROR)
+ ' occurred adding user '
+ @userName
end

[Erland Sommarskog]

[posted and mailed, please reply in news]

Bill S. (bi*********@hotmail.com) writes:

I a stored procedure that inserts a record into a table as
below.

The insert works OK, but if the insert violates a unique
indewx constraint on one of the columns, the proc terminates
immediately, and does NOT execute the 'if @@ERROR <> 0'
statement.

Am I doing something wrong, or do I need to set an attribute
somewhere?
begin tran

insert into Users
(UserName, UserPWD, Lname, Fname, UserDesc)
values (@userName, @userPWD, @lname, @fname, @userDesc)

if @@ERROR <> 0
begin
rollback tran
set @returnCode = -2
set @errMsg = 'SQL error '
+ convert(varchar(6), @@ERROR)
+ ' occurred adding user '
+ @userName
end

First, @@error is set after each statement, so @errMsg will never read
anything but "SQL Error 0 ...". Always save @@error in a local variable
before you do anything else with it.

So over to your question. Error handling in SQL Server is a messy topic,
and there are errors you cannot trap like this, because SQL Server
aborts the batch immediately. However, constraint violation as you
mention is not among those - unless the setting SET XACT_ABORT is ON.

So, assuming you are not using XACT_ABORT ON, the error handler should
be executed. But how do you know that it is not? What are @returnCode
and @errMsg? Local variables? Output parameters? If they are output
parameters, and run the procedure from Query Analyzer:

DECLARE @ret int, @errMsg varchar(200)
EXEC your_sp @userName, ..., @ret OUTPUT, @errMsg OUTPUT
SELECT @ret, @errMsg

My guess is that you are running the procedure from some client library
which traps the error, before you get to read the output parameters.

As I mentioned, error handling is really a messy topic, but I have
an article on error handling of my web site that may be of interest,
http://www.sommarskog.se/error-handling-II.html. I don't know exactly
on what level you are on; if you are fairly unexperienced with SQL
Server, you may feel overwhelmed, but you could browse it now, and
save it for later reading.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp