Format for a bigint parameter in Raiserror

_{Sunday, February 19, 2006 11:06 AM}

Having a small problem with RAISERROR that I've isolated to a small code sample:

The objective is to have the message string in Raiserror correctly report the value of a parameter that is a bigint. The following extract shows the problem:

ALTER PROCEDURE [dbo].[pr_Test]

@SomeNumber bigint

AS

BEGIN

SET NOCOUNT ON;

RAISERROR('Test Error Number: %d.',16,1,@SomeNumber)

END

As written, when the procedure is executed with the value 1 passed in the @SomeNumber parameter, the result is:

Msg 2786, Level 16, State 1, Procedure pr_Test, Line 19

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

If the datatype for @SomeNumber is changed to int, then the procedure executes correctly and reports 'Test Error Number: 1'. Any suggestions on how to fix this problem?

Regards,

Flavelle

_{Sunday, February 19, 2006 5:55 PM ✅Answered}

There seems to a limitation with the numeric data type while using the Raiserror statement.

My suggestion is to cast the bigint to a varchar Here is the modified code block

alter PROCEDURE [dbo].[pr_Test]

@SomeNumber BIGINT

AS

BEGIN

DECLARE @NewNumber VARCHAR(1000)

SET @NewNumber=CAST(@SomeNumber AS VARCHAR(100))

SET NOCOUNT ON;

RAISERROR('Test Error Number: %s',16,1, @NewNumber)

END

_{Monday, February 20, 2006 12:06 AM ✅Answered}

Have you looked at the definition for RAISERROR in Books Online?  It says bigint is not a valid datatype for that parameter.

argument

Is 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; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: int1, int2, int4, char, varchar, binary, or varbinary. No other data types are supported.

_{Monday, February 20, 2006 12:08 AM}

yes, i misread your post, and yes, casting/converting to a varchar datatype is a simple fix/workaround.

_{Monday, February 20, 2006 9:04 AM}

My thanks to you both - brokenrulz for the solution to the problem and Greg for reminding me that I have to remember to read the ENTIRE help file. Your reference to the supported datatypes for the parameters is deeply buried within the help topic.

Regards,

Flavelle

_{Thursday, April 26, 2007 9:45 AM | 13 votes}

Hello,

Help files have been updated on July 2006 (http://msdn2.microsoft.com/en-us/library/ms178592.aspx) and it says "To convert a value to the Transact-SQL bigint data type, specify %I64d" (the letter before 64 begin a capital i).

Not need to cast the bigint into an nvarchar. I tested under SQL 2005 + SP (version 9.00.3054.00) and it works great.

Laurent

_{Friday, June 1, 2007 9:33 AM}

I was facing the same problem and your suggestion saved the day for me. A big thanks.

_{Tuesday, September 30, 2014 8:56 AM}

You can try:

DECLARE @_bigint BIGINT = 123456789012345678
RAISERROR('Error with bigint %I64i', 16, 1, @_bigint)