[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 
