February 21, 2017 08:25 by
PeterIn this post, I will show you how to using bigint with FORMATMESSAGE. SQL Server 2016 added the FORMATMESSAGE function. According to Books On Line, FORMATMESSAGE constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.
So let's take a look at this new function, run the following
SELECT FORMATMESSAGE('Signed int %i, %i', 50, -50)
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);
Here is the output if you run that
--------------------------------------------
Signed int 50, -50
Unsigned int 50, 4294967246
Here is what the type specifications that you can use are
Type specification Represents
d or i Signed integer
o Unsigned octal
s String
u Unsigned integer
x or X Unsigned hexadecimal
We used i to denote a signed integer, we also used u to denote a unsigned integer
Let's look at another example, this time we are using a variable. The variable will be an integer and we are using i as the type specification
DECLARE @Val int = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);
Here is the output
---------------------------------------
The value you supplied 1 is incorrect!
That worked without a problem. Now let's use a variable of the bigint data type, we are using the same type specification as before
DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);
Here is the output
---------------------------------------------------------------------------
Error: 50000, Severity: -1, State: 1. (Params:).
The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.
As you can see that did not work, so what can we do?
One thing we can do is converting the value to a varchar and then use s as the type specification
DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %s is incorrect!',
CONVERT(VARCHAR(100),@Val));
You will again get this as output
---------------------------------------
So converting to varchar worked, but what if we want to use a bigint data type without converting to a varchar?
Another way is to use I64d as the type specification
DECLARE @Val bigint = 1<br />
SELECT FORMATMESSAGE('The value you supplied %I64d is incorrect!', @Val);
You will get this
---------------------------------------
The value you supplied 1 is incorrect!
So there you have it, if you want to use bigint with FORMATMESSGAE use I64d as the type specification, or convert to varchar and use s as the type specification.
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
