Check for error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check for error

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-13 : 10:46:59

Hello,

How can I check after an Update or Insert if an error occurs.
I suppose the error is in @@Error.

Maybe I should check if @@Error is null?
Should I do this? And how can I do this in my stored procedure?

Thanks,
Miguel

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-13 : 10:49:14

[code]if @@error <> 0 -- error
-- do something here
else -- no error
-- do something here[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-13 : 10:53:42

Thanks,

In the meanwhile I found the same code:
IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
END

In http://www.sqlteam.com/item.asp?ItemID=2463

Thanks,
Miguel

Kristen
Test

22859 Posts

Posted - 2007-02-13 : 11:18:00

"How can I check after an Update or Insert if an error occurs"

You might want to check @@ROWCOUNT too - if you are only expecting one row to be updated and all the rows in the table get updated that's probably an error too!


DECLARE @intErrNo int,
        @intRowCount int

BEGIN TRANSACTION
UPDATE U
SET MyCol = 'FOO'
FROM MyTable AS U
     JOIN MyOtherTable AS O
         ON O.MyPK = U.MyPK
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

IF @intErrNo = 0 AND @intRowCount = 1
BEGIN
    COMMIT
END
ELSE
BEING
    ROLLBACK
    PRINT 'Error Occurred'
END

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources