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[posted and mailed, please reply in news]
Bill S. (bill_sheets@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment