SQL Field Engineer Diary

6 yrs ago i wrote lots of T-SQL SP using old-school error handling way like:
DECLARE @ErrCode int
SET @@ErrCode = 0

–begin tran here
SELECT @ErrCode = @@ERROR

IF @ErrCode = 0
BEGIN
– stmt here
SELECT @ErrCode = @@ERROR
END

–
IF @ErrCode <> 0
– rollback tran here
ELSE
– commit tran here
It’s very boring to write such block again and again.. and the code becomes very dirty to read.

After SQL2k5, we can use TRY … CATCH … block to handle exception like other decent language as C#/Java!
———
Test script for nested SP

CREATE PROC Error01
AS
BEGIN TRY
DECLARE @X INT
SET @X = 1/0 — make some error
END TRY
BEGIN CATCH
PRINT ‘Error Detected 1′
SELECT ERROR_NUMBER() ERNumber,
ERROR_SEVERITY() Error_Severity,
ERROR_STATE() Error_State,
ERROR_PROCEDURE() Error_Procedure,
ERROR_LINE() Error_Line,
ERROR_MESSAGE() Error_Message

DECLARE @ERNumber int
DECLARE @Error_Severity int
DECLARE @Error_State int
DECLARE @Error_Message varchar(5000)

SELECT @ERNumber = ERROR_NUMBER(),
@Error_Severity = ERROR_SEVERITY() ,
@Error_State = ERROR_STATE(),
@Error_Message = ERROR_MESSAGE()
RAISERROR (@Error_Message,@Error_Severity,@Error_State)– haven’t found out how to pass error # yet
END CATCH
go
CREATE PROC Error02
AS
BEGIN TRY
EXEC Error01
END TRY
BEGIN CATCH
PRINT ‘Error Detected 2′
SELECT ERROR_NUMBER() ERNumber,
ERROR_SEVERITY() Error_Severity,
ERROR_STATE() Error_State,
ERROR_PROCEDURE() Error_Procedure,
ERROR_LINE() Error_Line,
ERROR_MESSAGE() Error_Message
END CATCH
GO
EXEC Error02
GO

– The result :
Error Detected 1
ERNumber    Error_Severity Error_State Error_Procedure  Error_Line  Error_Message
———– ————– ———– —————- ———– ———————————
8134        16             1           Error01          6           Divide by zero error encountered.
(1 row(s) affected)
Error Detected 2
ERNumber    Error_Severity Error_State Error_Procedure  Error_Line  Error_Message
———– ————– ———– —————- ———– ———————————
50000       16             1           Error01          26          Divide by zero error encountered.
(1 row(s) affected)

Conclusion:
1. It’s easier to write error handling code in 2k5. Thinking in the same way as C#.
2. Error handling code should be placed at the end of the script.
3. Be careful for trans control.

I do some tests below to see the behavior of translation savepoint when I considering nested SP.

Test 1
———-
SET NOCOUNT ON
CREATE TABLE TranTest
(
 ID int IDENTITY(1,1) PRIMARY KEY,
 Txt varchar(50)
)
go
 
BEGIN TRAN tran01
 INSERT INTO TranTest(Txt)
 values(‘Tran 01′)
 
 SAVE TRAN tran02
 INSERT INTO TranTest(Txt)
 values(‘Tran 02 first time’)
 ROLLBACK TRAN tran02 — Rollback here
 
 SAVE TRAN tran02
  INSERT INTO TranTest(Txt)
 values(‘Tran 02 second time’)
 
 COMMIT TRAN tran01
 
SELECT * FROM TranTest
go
 
DROP TABLE TranTest
GO
 
–Result
ID          Txt
———– ————————————————–
1           Tran 01
3           Tran 02 second time
 
This test tells me that when the begin of translation being committed, all the savepoint between it will be committed too. But those changes in already rollback savepoint won’t be saved.
 
Test 2
————–
SET NOCOUNT ON
CREATE TABLE TranTest
(
 ID int IDENTITY(1,1) PRIMARY KEY,
 Txt varchar(50)
)
go
BEGIN TRAN tran01
 INSERT INTO TranTest(Txt)
 values(‘Tran 01′)
 
 SAVE TRAN tran02
 INSERT INTO TranTest(Txt)
 values(‘Tran 02 first time’)
 
 
 SAVE TRAN tran02
  INSERT INTO TranTest(Txt)
 values(‘Tran 02 second time’)
 
 ROLLBACK TRAN tran02
 COMMIT TRAN tran01
 
SELECT * FROM TranTest
go
 
DROP TABLE TranTest
GO
 
–result
ID          Txt
———– ————————————————–
1           Tran 01
2           Tran 02 first time
 
This test tells me that the savepoint could be same name, and when rollback savepoint the most recent one will be rollbacked..
 
Test 3
——————
SET NOCOUNT ON
CREATE TABLE TranTest
(
 ID int IDENTITY(1,1) PRIMARY KEY,
 Txt varchar(50)
)
go
BEGIN TRAN tran01
 INSERT INTO TranTest(Txt)
 values(‘Tran 01′)
 
 SAVE TRAN tran02
 INSERT INTO TranTest(Txt)
 values(‘Tran 02 first time’)
 
 
 SAVE TRAN tran02
  INSERT INTO TranTest(Txt)
 values(‘Tran 02 second time’)
 
 COMMIT TRAN tran02 — try to commit savepoint
 COMMIT TRAN tran01 — COMMIT others change
 
SELECT * FROM TranTest
go
 
DROP TABLE TranTest
GO
 
–Result
Msg 3902, Level 16, State 1, Line 16
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
ID          Txt
———– ————————————————–
1           Tran 01
2           Tran 02 first time
3           Tran 02 second time

The last test tells me that ONLY Translation could be committed! When you trying to commit a savepoint, the whole translation will be committed. And if you select @@trancount, you can see the # is less 1 now.
 
Conclusion:
1. SELECT @TC = @@trancount to see if to open a new translation or a savepoint.
2. Check if @TC = 0 when trying to commit a translation. If @TC <>0, that means the translation is not started by this SP, but outer SP..
3. Rollback to Savepoint everytime error happens.