Skip to content

SQL Server Error Messages - Msg 234

Error Message

Server: Msg 234, Level 16, State 2, Line 1
There is insufficient result space to convert a money value to varchar / nvarchar / char / nchar.

Causes

This error occurs when converting, either implicitly or explicitly using the CAST or CONVERT functions, a MONEY value to a VARCHAR, NVARCHAR, CHAR or NCHAR value and the length specified is not long enough to accommodate the MONEY value.

To illustrate, here’s how the error can occur using an implicit conversion of a MONEY data type to a VARCHAR data type:

DECLARE @Money MONEY
DECLARE @VarChar VARCHAR(8)

SET @Money = 100000.00
SET @VarChar = @Money

Msg 234, Level 16, State 2, Line 5
There is insufficient result space to convert a money value to varchar.

The total number of characters needed to convert the MONEY to VARCHAR in this example is 9 and not 8 as the decimal point has to be included as a character in the output.

Here’s another way of encountering the error using an explicit conversion from a MONEY data type to NVARCHAR data type using either the CAST or CONVERT functions:

DECLARE @Money MONEY
SET @Money = 100000.00
SELECT CAST(@Money AS NVARCHAR(8))

Msg 234, Level 16, State 2, Line 3
There is insufficient result space to convert a money value to nvarchar.

A similar way of getting this error is with negative MONEY values, as illustrated in the following, which uses the CONVERT function this time:

DECLARE @Money MONEY
SET @Money = -100000.00
SELECT CONVERT(CHAR(9), @Money)

Msg 234, Level 16, State 2, Line 3
There is insufficient result space to convert a money value to varchar.

Solution / Work Around

This error can easily be avoided by making sure that the length of the VARCHAR, NVARCHAR, CHAR or NCHAR variable is long enough to accommodate the length of the MONEY value, taking into account the decimal point and the negative sign in the case of negative values.

Using the first example earlier using the implicit conversion, by increasing the length of the VARCHAR local variable to 9 (or higher), the error will be avoided:

DECLARE @Money MONEY
DECLARE @VarChar VARCHAR(9)

SET @Money = 100000.00
SET @VarChar = @Money

Similarly, the error can be avoided in the explicit conversion example earlier by also increasing the length of the NVARCHAR local variable to 9 (or higher):

DECLARE @Money MONEY
SET @Money = 100000.00
SELECT CAST(@Money AS NVARCHAR(9)) AS [NVarCharValue]

VarCharValue
--------------
100000.00

As for the negative MONEY value example earlier, make sure that the length of the CHAR data type takes into consideration both the decimal point as well as the negative sign. Increasing the length of the CHAR data type from 9 to 10 will avoid this error:

DECLARE @Money MONEY
SET @Money = -100000.00
SELECT CONVERT(CHAR(10), @Money) AS [NegativeMoney]

NegativeMoney
---------------
-100000.00