Stored PRocedure: @@ERROR problem
-
Hi,
I'm trying to detect when an error occurs within a sp and returning the error number if not 0. But it doesn't seem to want to give me back the error code! I have an update statement that updates a row in a table that has a relationship to another table. When I run it knowing that the @USER_ID doesn't exist in the other table, I get the error as output ("Update conflicted with COLUMN FOREIGN KEY constraint....") but it doesn't return the error number. Why is this??
sp is:
ALTER PROCEDURE dbo.sp_DOMaintAssess(
@User_ID Integer = 0,
)
AS
DECLARE @ERROR int
UPDATE CAPA_MAIN SET MaintAssess = @User_ID WHERE ID = 1
SELECT @ERROR = @@ERROR
IF @ERROR != 0
BEGIN
PRINT 'The Error No Is ' + CONVERT(varchar, @ERROR)
END
RETURN
-
This may be an error that stops the code. DRI errors or other major errors like column name doesn't exists causes the code execution to end, never entering the err handler.
You might be interested in reading this :
-
Try RETURN @ERROR. The RETURN statement will return any number that you associate with it. I would also change the SELECT @ERROR to SET @ERROR.
You will then be able to sp's RETURN statement with your application.
BTW it isn't a good practice to name your sp's sp_... The system will ALWAYS 1st go to master to find it and THEN look in your DB...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thanks for the pointers guys, I'll give it a go.
-
Doh!!! missed an easy one here
-
Remi .... u just on a mission to get ur "posts" up to Steve and Frank's scores or summat???
-
He's definitely on a blistering pace.
-
I just have a little more time for this ATM.
Don't forget that the more I post, the more I learn. On many cases here I have to search 10-15 minutes to get the answer right.
And like the expression says, I'll never have time to run in all the problems all of you will be running into in my lifetime. So I'm kind of at school here
.Think I'm gonna pass??
-
I never suggested your posts were fluff. You've helped me many times Remi. tx.
-
My lifetime daily post average as gone up almost 2 points in the last 2-3 weeks (from 3.2 to 5.2 with about 400+ days since I joined). I'm still behind Frank on that matter though. He has an average of almost 6 posts/day.
-
I never understood you didn't like my postings
. -
I just want to let everybody know that errorhandlings in sql server 2000 do not work!!!.
Eg.
UPDATE permanent_tbl2
SET ...
FROM #temp ....
SELECT @err = @@error
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'you got error!!' ------it will never display this message. In fact it will never looks at the IF statement if there is an error.
RETURN @err
END
D. Obrien, even if you took that update statement and run it in QA it still won't work!!!.
This is a bug in sql 2000! -- this a
If you can capture an error for UPDATE/INSERT please let me know. I would be highly interested.
In saying that there is a way around this......but doesn't look 'clean'.
-
Depends of the severity of the error. Sometimes the execution stops right at the error and exits the proc. Otherwise the code you posted should work.
-
Sometimes Transactions can get screwed up as well if you handle them explicitly. I've had good luck using:
SET XACT_ABORT ON
to help that.
-
So to cut a long story short, what should I do to reliably catch teh error (and rollback the transaction)?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply