|
|
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.
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.
Figure 1: Error Message 234 - Insufficient Result Space to Convert 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.
Figure 2: Error Message 234 - Insufficient Result Space to Convert 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]
| NVarCharValue |
|---------------|
| 100000.00 |
Figure 3: Error Message 234 - CAST Solution 1
Alternatively, instead of specifying the length of the NVARCHAR data type in the CAST function, you can just leave it out and the default length of 30 will be used.
DECLARE @Money MONEY
SET @Money = 100000.00
SELECT CAST(@Money AS NVARCHAR) AS [NVarCharValue]
| NVarCharValue |
|---------------|
| 100000.00 |
Figure 4: Error Message 234 - CAST Solution 2
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 |
Figure 5: Error Message 234 - CONVERT Solution 1
As was the case with the CAST conversion function, you can just leave out the length of the CHAR, VARCHAR, NCHAR or NVARCHAR in the CONVERT function and the default length of 30 will be assigned.
DECLARE @Money MONEY
SET @Money = -100000.00
SELECT CONVERT(CHAR, @Money) AS [NegativeMoney],
LEN(CONVERT(CHAR, @Money)) AS [NegativeMoneyCharLength]
| NegativeMoney | NegativeMoneyCharLength |
|--------------------------------|-------------------------|
| -100000.00 | 30 |
Figure 6: Error Message 234 - CONVERT Solution 2
|