469,964 Members | 1,670 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,964 developers. It's quick & easy.

Help: Prob handling SQL error in stored proc

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
Jul 20 '05 #1
1 4596
[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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jon LaRosa | last post: by
2 posts views Thread by Mike | last post: by
4 posts views Thread by William F. Robertson, Jr. | last post: by
8 posts views Thread by CarpetMnuncher! | last post: by
reply views Thread by mirandacascade | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.