Skip to main content

THROW - New Addition for Error Handler in SQL Server Denali

Now we have so many options and ways to handle errors in SQL Server. And It is very easy today because of the addition or enhancement of error handling in SQL Server.  You can read all features of SQL Server Denali.

We were used @@Error and Rollback Transaction wrote after every t-sql statements in SQL Server 2000. After that we used to start Try and Catch block to handle errors in SQL Server 2005 and 2008. Among all SQL Server 2012 have one more addition in error handler, which is introduced as THROW.

Syntax :
THROW [ { error_number | @local_variable },
        { message | @local_variable },
    { state | @local_variable }
] [ ; ]

Let's drive with Error handling and THROW statements.

An Example :
-- Creating database and table
CREATE DATABASE ThrowTestDB

go

USE ThrowTestDB

go 

IF ( Object_id('ThrowTestTab') > 0 )
  DROP TABLE ThrowTestTab

go

CREATE TABLE ThrowTestTab
  (
     Id   INT PRIMARY KEY,
     Name VARCHAR(10)
  )

go 
Now we will look how the error handling works in all SQL Server versions and new addition to that.

Error handling in SQL Server 2000
BEGIN TRANSACTION

DECLARE @ErrorNum INT

-- Inserting same value in Id column to raise error
INSERT INTO ThrowTestTab
            (Id,
             Name)
SELECT 1,
       'Throw-1'
UNION ALL
SELECT 1,
       'Throw-2'

SET @ErrorNum = @@ERROR

IF @ErrorNum <> 0
  BEGIN
      COMMIT TRANSACTION
  END
ELSE
  BEGIN
      ROLLBACK TRANSACTION

      RAISERROR ('Error occured while inserting data',
                 16,
                 1)
  END

GO 
Error handling in SQL Server 2005/2008
DECLARE @ErrorMessage VARCHAR(500),
        @ErrorSev     INT,
        @ErrorState   INT

BEGIN try
    BEGIN TRANSACTION

    -- Inserting same value in Id column to raise error
    INSERT INTO ThrowTestTab
                (Id,
                 Name)
    SELECT 1,
           'Throw-1'
    UNION ALL
    SELECT 1,
           'Throw-2'

    COMMIT TRANSACTION
END try

BEGIN catch
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION

    SELECT @ErrorMessage = Error_message(),
           @ErrorSev = Error_severity(),
           @ErrorState = Error_state()

    RAISERROR (@ErrorMessage,
               @ErrorSev,
               @ErrorState)
END catch

GO 
Error handling in SQL Server 2012
BEGIN try
    BEGIN TRANSACTION

    -- Inserting same value in Id column to raise error
    INSERT INTO ThrowTestTab
                (Id,
                 Name)
    SELECT 1,
           'Throw-1'
    UNION ALL
    SELECT 1,
           'Throw-2'

    COMMIT TRANSACTION
END try

BEGIN catch
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION ;
      Throw;
END catch

GO 
Note :
Before THROW statement semicolon(;) is required. You can see the difference between THROW and RAISERROR here.

Comments

  1. You may be interested in a more comprehensive series:

    Exception handling in T-SQL/TRY…CATCH – Underappreciated features of Microsoft SQL Server - http://beyondrelational.com/blogs/nakul/archive/2011/02/24/exception-handling-in-t-sql-try-catch-underappreciated-features-of-microsoft-sql-server.aspx

    This explains the history of how exception handling has evolved over time from SQL 2000.

    You may also want to look at:
    Sunset for RAISERROR and sunrise for THROW – SQL 11 (“Denali”) - http://beyondrelational.com/blogs/nakul/archive/2011/02/28/sunset-for-raiserror-and-sunrise-for-throw-sql-11-denali.aspx

    ReplyDelete

Post a Comment