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 :
Let's drive with Error handling and THROW statements.
An Example :
Error handling in SQL Server 2000
Before THROW statement semicolon(;) is required. You can see the difference between THROW and RAISERROR here.
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) ) goNow 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 GOError 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 GOError 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 GONote :
Before THROW statement semicolon(;) is required. You can see the difference between THROW and RAISERROR here.
You may be interested in a more comprehensive series:
ReplyDeleteException 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