SQL Server Batch Error Handling Problem.

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

From stackoverflow

• 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
  

  John Sansom : +1: A good clear and concise answer.

  James : +1 Thanks for the thorough response!