Format for a bigint parameter in Raiserror

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

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

|||

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.

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

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

|||

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

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