http://www.sqlservercentral.com/blogs/sql-development-wizard/2012/05/31/a-new-neat-feature-in-sql-server-2012-is-throw/

Printed 2014/09/17 01:52AM

A new neat feature in SQL Server 2012 is THROW

2012/05/31


In SQL Server 2005 we got the TRY…CATCH construct which was a big help for developers to effectively handle errors within their T-SQL code. Prior to that the developers had to use the @@ERROR in-built system function to check for errors and they had to check for error conditions after every operation! That sucked big and too often the developers forgot.




Developers also had to use the RAISERROR (notice the spelling mistake that we are forced to live with) statement to show an error message. But there where many issues:




And even if you could go around most of the issues, example by embed the original error details as a custom message passed to RAISERROR, it was always hard to know what happened just by reading the T-SQL since you had to know the parameters, like ErrorSeverity.


In SQL Server 2012, you can use new THROW statement, borrowed from throw in the .NET model) and it can be used in two ways:

 
 
Note that THROW() need the preceding statement to end with a proper statement terminator. So start using those semi-colons!



SQL Server 2008


SET NOCOUNT ON

BEGIN TRY
     SELECT 1/0
END TRY
BEGIN CATCH
     DECLARE @ErrorNumber int
     DECLARE @ErrorState int
     DECLARE @ErrorSeverity int
     DECLARE @ErrorLine int
     DECLARE @ErrorProcedure NVARCHAR(MAX) 
     DECLARE @ErrorMessage NVARCHAR(MAX) 
     DECLARE @UserName NVARCHAR(256) 
     DECLARE @HostName NVARCHAR(128)

     SELECT  @ErrorNumber = ERROR_NUMBER() 
     ,@ErrorState = ERROR_STATE() 
     ,@ErrorSeverity = ERROR_SEVERITY() 
     ,@ErrorLine = ERROR_LINE() 
     ,@ErrorProcedure = ERROR_PROCEDURE() 
     ,@ErrorMessage = ERROR_MESSAGE() 
     ,@UserName = SUSER_SNAME() 
     ,@HostName = Host_NAME() 

     SELECT @ErrorNumber,@ErrorState,@ErrorSeverity,@ErrorLine,@ErrorProcedure,@ErrorMessage,@UserName,@HostName
  
     RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )
END CATCH


Msg 50000, Level 16, State 1, Line 26
Divide by zero error encountered.

SQL Server 2012

SET NOCOUNT ON;

BEGIN TRY
   SELECT 1/0;
END TRY
BEGIN CATCH
    THROW;
END CATCH


Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.



Start lobbying your developer to use the new THROW statement and you will get better error handling and easier problem detection. This equals more time for you to do the fun SQL stuff!

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.