FORMATMESSAGE

Description

Constructs a message from an existing message in sys.messages or from a provided string. (FORMATMESSAGE in Transact-SQL).

Sample Source Pattern

Since Snowflake does not support FORMATMESSAGE function, the FORMATMESSAGE_UDF is added to simulate its behavior.

Syntax

SQL Server

FORMATMESSAGE ( { msg_number  | ' msg_string ' | @msg_variable} , [ param_value [ ,...n ] ] )  

Examples

SQL Server

Code:

IN -> SqlServer_01.sql

SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS RESULT;

Result:

                                                     RESULT|
-----------------------------------------------------------+
This is the first variable and this is the second variable.|

Snowflake

Code:

OUT -> SqlServer_01.sql

SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('This is the %s and this is the %s.', ARRAY_CONSTRUCT('first variable', 'second variable')) AS RESULT;

Result:

RESULT                                                     |
-----------------------------------------------------------+
This is the first variable and this is the second variable.|

Related EWIs

• SSC-FDM-TS0008: FORMATMESSAGE function was converted to UDF.