Parameters in RAISERROR

Question

Parameters in RAISERROR

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
March 9, 2021 at 12:00 am

#3850269

Comments posted to this topic are about the item Parameters in RAISERROR

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 1

DECLARE @d int = 1
RAISERROR (' %s is the current value', 1, 0, @d)

Running this result in error 2786, The data type of substitution parameter 1 does not match the expected type of the format specification.

%s in the msg_str cannot be used with @d as int.

Bredon SSC Journeyman Points: 88 More actions · Answer 2

Strange. Using SQL Server 2019:

Declare @d tinyint
set @d = 1
RAISERROR(' %s is the current value', 1, 0, @d);

Get error message:

The data type of substitution parameter 1 does not match the expected type of the format specification.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 3

-- correct

declare @d tinyint = NULL

RAISERROR(' %s is the current value', 1, 0, @d)

-- incorrect

declare @d tinyint = 1

RAISERROR(' %s is the current value', 1, 0, @d)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

The documents you linked say that it MUST be CHAR or VARCHAR or be able to be implicitly converted to those datatypes. According to this website:

https://www.sqlshack.com/implicit-conversion-in-sql-server/

The implicit conversion for CHAR or VARCHAR only allows VARBINARY and BINARY, so none of the answers are correct UNLESS we assume that the variable @d is NULL. Then I think that implicit conversion can be a bit more flexible as I think all datatypes support NULL.

TL;DR - I don't think any of the answers are correct.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jason- SSCrazy Points: 2795 More actions · Answer 5

Of the list of types provided in the answer explanation, only CHAR, NCHAR, VARCHAR and NVARCHAR types will work.

All the other datatypes fail with the exception "The data type of substitution parameter 1 does not match the expected type of the format specification".

This makes sense since the underlying call is to the C printf function and C is strongly typed.

Code used to test each datatype

DECLARE @vc VARCHAR(128) = 'Test VARCHAR'
RAISERROR(' %s is the current value', 1, 0, @vc)
GO

DECLARE @nvc NVARCHAR(128) = 'Test NVARCHAR'
RAISERROR(' %s is the current value', 1, 0, @nvc)
GO

DECLARE @c CHAR(128) = 'Test CHAR'
RAISERROR(' %s is the current value', 1, 0, @c)
GO

DECLARE @nc VARCHAR(128) = 'Test NCHAR'
RAISERROR(' %s is the current value', 1, 0, @nc)
GO

DECLARE @ti TINYINT = 1
RAISERROR(' %s is the current value', 1, 0, @ti)
GO

DECLARE @si SMALLINT = 2
RAISERROR(' %s is the current value', 1, 0, @si)
GO

DECLARE @i INT = 2147483647
RAISERROR(' %s is the current value', 1, 0, @i)
GO

DECLARE @b BINARY(8) = convert(BINARY(8), 'Test BINARY')
RAISERROR(' %s is the current value', 1, 0, @b)
GO

DECLARE @vb VARBINARY(8) = convert(VARBINARY(8), 'Test VARBINARY')
RAISERROR(' %s is the current value', 1, 0, @vb)
GO

-

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 6

Apologies, ticked the wrong answer box for some reason. This has been corrected.

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 7

In the documentation it is:

Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.

The answer you marked correct is:

answered char, varchar, nchar, or nvarchar

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 8

I checked my answer against the Microsoft Docs and it states:

argument
Are the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.

So, I'm wondering why when I selected tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary I was wrong. Did I miss something?

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Jason- SSCrazy Points: 2795 More actions · Answer 9

In order to use one of the other data types we have to use a different type specification. While you can use all of those listed datatypes in RAISERROR you can only use (n)char, (n)varchar with %s

Type specification
d or i = Signed integer ex: 'The value is %d'
o = Unsigned octal ex: 'The value is %o'
s = String ex: 'The value is %s'
u = Unsigned integer ex: 'The value is %u'
x or X = Unsigned hexadecimal ex: 'The value is %x'

-

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 10

Jason- wrote:

In order to use one of the other data types we have to use a different type specification. While you can use all of those listed datatypes in RAISERROR you can only use (n)char, (n)varchar with %s
Type specification Represents d or i = Signed integer ex: 'The value is %d' o = Unsigned octal ex: 'The value is %o' s = String ex: 'The value is %s' u = Unsigned integer ex: 'The value is %u' x or X = Unsigned hexadecimal ex: 'The value is %x'

You got me there

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com