SQL Server Performance Forum – Threads Archive

Error handling with two different condition?

Hi,
What is the difference between these two Error trapping conditions? Any preferences?
SET @Error = @@ERROR vs SET @Error = @Error + @@ERROR ? Code sample:
DECLARE @Error int CREATE TABLE #t1
(id int PRIMARY KEY
,Description varchar(25)) INSERT INTO #t1(id, Description)
VALUES (1, ‘Test1’) INSERT INTO #t1(id, Description)
VALUES(1, ‘Test2’) SET @Error = @@ERROR
PRINT @Error
GO DECLARE @Error int INSERT INTO #t1(id, Description)
VALUES (1, ‘Test3’) SET @Error = @Error + @@ERROR
PRINT @Error
GO
drop table #t1
GO
Results: (1 row(s) affected)
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint ‘PK__#t1_______________58DF8AE5’. Cannot insert duplicate key in object ‘dbo.#t1’.
The statement has been terminated.
2627
Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint ‘PK__#t1_______________58DF8AE5’. Cannot insert duplicate key in object ‘dbo.#t1’.
The statement has been terminated.
Thanks, Name
———
Dilli Grg (1 row(s) affected)

Because @Error is NULL when you concactenate @Error + @@ERROR it sets it to NULL still (in the second batch). NULL concactenates to NULL… Bear in mind that PRINT and SELECT display NULL different in query analyzer/ssms. If you change the print to a select you can see the difference. Edit: Frist! <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />

In first statement you are populating @Error variable with @@error…
In second statement you are doing the same but @error variable is not initialized means it is null
so that you are not getting the error number.. Both will be the same but you have to initialize @error in second set… DECLARE @Error int
set @Error = 0
INSERT INTO #t1(id, Description)
VALUES (1, ‘Test3’)
SET @Error = @Error + @@ERROR
PRINT @Error
GO
drop table #t1
GO
SET @Error = @Error + @@ERROR
PRINT @Error
GO
drop table #t1
GO Mohammed U.

Hi,
As you have posted your thread under 2005 section, so i assume your question must be related to sql 2005.
So note that in 2005 error handling has changed alot and much better.
In 2005, there is introduction of Try & catch Block.
So do read about them in BOL.

Check this article which is applicable to using @@error variable and also shows about the idea of using TRY CATCH block in 2005. http://www.sommarskog.se/error-handling-I.html

Thanks everyone for your response. Ranjitjain,
Yes, I am aware of the error handling in SQL 2005 and have used several times but I was just curious about these two different ways to assign the values. Mohammed and Haywood have given somewhat clear explanation and that’s what I was looking for actually. Thanks, Name
———
Dilli Grg (1 row(s) affected)

]]>