SQL Error Trapping
I have been doing some further digging on the ERROR_STATE() function for SQL Server and have found that the State global variable is managed by the SQL Engine, not the user. This allows SQL Engineers to track different SQL Server Engine subroutines causing an error with the same ERROR_NUMBER(). So, if search for help on an error message and it has a different STATE than the one returned in your message, it may likely not apply to your problem.
How do you know what the state is? In SQL Server Management Studio (SSMS) the error message is displayed when running a query in the console or message window. If you are using the SQL Server TRY/CATCH error handling in your code it is a lot easier and more robust to debug what is going on.
In the CATCH clause of your SQL you use the ERROR_NUMBER, ERROR_MESSAGE(), ERROR_STATE() and ERROR_LINE() methods to capture more information regarding what happened to cause the error. ERROR_STATE() can be used in conjunction with ERROR_NUMBER() to research on the web for help solving the issue. ERROR_LINE() is helpful for pointing to the specific line of code in your SQL that caused the error to be raised.
You can see error line in output from running SQL without using TRY/CATCH. However, you can’t do anything with it programmatically; at least not without jumping through a lot of hoops. However, if you use TRY/CATCH, you can capture all this information quite handily in the CATCH clause.
Moreover, if you have nested TRY/CATCH blocks, the values are segregated in each CATCH clause, even if multiple CATCH clauses are fired.
Here’s a sample TRY/CATCH demonstration with nested TRY/CATCH routines.
USE TempDB
GO
CREATE PROCEDURE Test_SP
@Var1 INT
,@Var2 INT
AS
BEGIN TRY
-- Generate a divide by zero error
SELECT 1/@Var1;
BEGIN TRY
SELECT CAST(999999999999 AS INT) * CAST(@Var2 AS INT)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ErrorNumber, ERROR_State() as ErrorState, ERROR_LINE() AS ErrorLine
END CATCH
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ErrorNumber, ERROR_STATE() AS ErrorState, ERROR_LINE() AS ErrorLine
END CATCH;
GO
EXEC Test_SP 0, 1
EXEC Test_SP 1, 9999999
GO
DROP PROCEDURE Test_SP
While this may be old information for seasoned TSQL programmers, it may be helpful for those of you learning to write stored procedures, triggers or multi-statement functions. Leave your comments below or drop me a note at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Managing DB2 for z/OS Application Performance
I want to focus on the high-level aspects of performance management from the perspective of the application. Furthermore, I will discuss this in terms of DB2 for z/OS.
Featured White Paper(s)
Optimizing analytic workloads using DB2 10.5 with BLU Acceleration
BLU Acceleration is a new collection of technologies for analytic queries that are introduced in DB2 for Linux, UNIX, and Win… (read more)
Featured Script
dba3_NorthWind_0080_Implement_Materialized_View_Invoices_Article
View it now! – Part II: Banishing view timeouts by implementing user "materialized views"… (read more)