ERROR_MESSAGE (Transact-SQL)
source link: https://learn.microsoft.com/en-us/sql/t-sql/functions/error-message-transact-sql?view=sql-server-ver16
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
ERROR_MESSAGE (Transact-SQL)
- SQL Server
- Docs navigation tips
- Previous versions 2005-2014
- Overview
- Business continuity
- Database design
- Development
- Internals & Architecture
- Installation
- Migrate & load data
- Manage, monitor, & tune
- Query data
- Reporting & Analytics
- Security
- Tools
- Tutorials
- SQL Server on Linux
- SQL on Azure
- Azure Arc
- Resources
- Reference
- Azure Data CLI azcli
- Database samples
- Errors & events
- Event classes
- Native interfaces
- SQL PowerShell
- System catalog views
- System compatibility views
- System dynamic management views
- System functions
- System information schema views
- System stored procedures
- System tables
- Transact-SQL (T-SQL) Reference
- Transact-SQL (T-SQL) Reference
- Date & time
- hierarchyid methods (database engine)
- Numeric
- String & binary
- Spatial geography & instances (geography Data Type)
- Spatial geometry & instances (geometry Data Type)
- Data types
- Functions
- Language elements
- Queries
- Statements
- xQuery
ERROR_MESSAGE (Transact-SQL)
- Article
- 05/23/2023
In this article
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
This function returns the message text of the error that caused the CATCH block of a TRY...CATCH construct to execute.
Transact-SQL syntax conventions
Syntax
ERROR_MESSAGE ( )
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Return Types
nvarchar(4000)
Return Value
When called in a CATCH block, ERROR_MESSAGE
returns the complete text of the error message that caused the CATCH
block to run. The text includes the values supplied for any substitutable parameters - for example, lengths, object names, or times.
ERROR_MESSAGE
returns NULL when called outside the scope of a CATCH block.
Remarks
ERROR_MESSAGE
supports calls anywhere within the scope of a CATCH block.
ERROR_MESSAGE
returns a relevant error message regardless of how many times it runs, or where it runs within the scope of the CATCH
block. This contrasts with a function like @@ERROR, which only returns an error number in the statement immediately following the one that causes an error.
In nested CATCH
blocks, ERROR_MESSAGE
returns the error message specific to the scope of the CATCH
block that referenced that CATCH
block. For example, the CATCH
block of an outer TRY...CATCH construct could have an inner TRY...CATCH
construct. Inside that inner CATCH
block, ERROR_MESSAGE
returns the message from the error that invoked the inner CATCH
block. If ERROR_MESSAGE
runs in the outer CATCH
block, it returns the message from the error that invoked that outer CATCH
block.
Examples
A. Using ERROR_MESSAGE in a CATCH block
This example shows a SELECT
statement that generates a divide-by-zero error. The CATCH
block returns the error message.
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Here is the result set.
-----------
(0 row(s) affected)
ErrorMessage
----------------------------------
Divide by zero error encountered.
(1 row(s) affected)
B. Using ERROR_MESSAGE in a CATCH block with other error-handling tools
This example shows a SELECT
statement that generates a divide-by-zero error. Along with the error message, the CATCH
block returns information about that error.
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Here is the result set.
-----------
(0 row(s) affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- --------------- ---------- ----------------------------------
8134 16 1 NULL 4 Divide by zero error encountered.
(1 row(s) affected)
See Also
sys.messages (Transact-SQL)
TRY...CATCH (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)
RAISERROR (Transact-SQL)
@@ERROR (Transact-SQL)
Errors and Events Reference (Database Engine)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK