SQL Server Batch Error Handling Problem.

Question

How do I get this batch of SQL to get to the RollBack Transaction part at the end? SQL just stops halts script execution on the bad line of code. I know I can use a try/catch construct but i'm more interested in how this was this handled before SQL added try/catch.

BEGIN TRAN

CREATE TABLE TempTable (c1 INT NULL)

INSERT INTO TempTable (c1) SELECT 1

INSERT INTO TempTable (c1) SELECT 'ABS'

IF (@@ERROR = 0) 
BEGIN
    PRINT 'no error'
    COMMIT TRAN
END
    ELSE
BEGIN
    PRINT 'error' -- Why does it never get here???????
    ROLLBACK TRAN
END

Answer 1

In this case, your 'ABS' is batch aborting because it's a CAST error. Explanation here, at Erland Sommarskog's excellent article

You have to read this article. More than you ever needed to know about SQL error handing.

Also, you have to test each statement. If the first INSERT failed, you'd still carry on executing (unless you had XACT_ABORT ON.

BEGIN TRAN

CREATE TABLE TempTable (c1 INT NULL)

INSERT INTO TempTable (c1) SELECT 1
IF @@ERROR <> 0
    GOTO errhandler

INSERT INTO TempTable (c1) SELECT 'ABS'
IF @@ERROR <> 0
    GOTO errhandler

PRINT 'no error'
COMMIT TRAN
GOTO exitpoint

errhandler:
PRINT 'error' -- Why does it never get here???????
ROLLBACK TRAN

exitpoint:

If you have SQL Server 2000 then you don't have many options except to add more checks, ISNUMERIC etc.

If you have SQL Server 2005, then you should really use the new techniques. Pretty much all code and execution errors are caught cleanly.

BEGIN TRY
    BEGIN TRAN

    CREATE TABLE TempTable (c1 INT NULL)

    INSERT INTO TempTable (c1) SELECT 1

    INSERT INTO TempTable (c1) SELECT 'ABS'

    PRINT 'no error'
    COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT 'error' --It will get here for SQL 2005
    ROLLBACK TRAN
END CATCH