The msg_str argument in RAISERROR (Transact-SQL) described the structure of message string and the use of parameters in the string. It’s very useful if we want to build dynamic text or string with multiple parameters. In this article, we are listed variety of format sample.
Conversion specifications format
% [[flag] [width] [. precision] [{h | l}]] type
flag
Is a code that determines the spacing and justification of the substituted value.
| Code | Prefix or justification | Description |
|---|---|---|
| – (minus) | Left-justified | Left-justify the argument value within the given field width. |
| + (plus) | Sign prefix | Preface the argument value with a plus (+) or minus (-) if the value is of a signed type. |
| 0 (zero) | Zero padding | Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored. |
| # (number) | 0x prefix for hexadecimal type of x or X | When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored. |
| ‘ ‘ (blank) | Space padding | Preface the output value with blank spaces if the value is signed and positive. This is ignored when included with the plus sign (+) flag. |
width
Is an integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.
An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.
precision
Is the maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.
For integer values, precision is the minimum number of digits printed.
An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.
{h | l} type
Is used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.
| Type specification | Represents |
|---|---|
| d or i | Signed integer |
| o | Unsigned octal |
| s | String |
| u | Unsigned integer |
| x or X | Unsigned hexadecimal |
Examples
string format
SELECT FORMATMESSAGE(‘Hello %s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %-20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello % 20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %5.3s!’, ‘TEST12345678’);
number format
SELECT FORMATMESSAGE(‘Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d’, 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE(‘Signed int with up to 3 leading zeros %03i’, 5);
SELECT FORMATMESSAGE(‘Signed int with up to 20 leading zeros %020i’, 5);
SELECT FORMATMESSAGE(‘Signed int with leading zero 0 %020i’, -55);
SELECT FORMATMESSAGE(‘Bigint %I64d’, 3000000000);
SELECT FORMATMESSAGE(‘Unsigned int %u, %u’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned octal %o, %o’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned hexadecimal %x, %X, %X, %X, %x’, 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned octal with prefix: %#o, %#o’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x’, 11, 11, -11, 50, -50);