|
|
Error Message
Server: Msg 292, Level 16, State 2, Line 1
There is insufficient result space to convert a
smallmoney value to varchar/nvarchar.
Causes
This error occurs when converting, either implicitly or explicitly using the CAST or CONVERT functions, a SMALLMONEY value to a VARCHAR or NVARCHAR value and the length of the VARCHAR or NVARCHAR is not long enough to accommodate the SMALLMONEY value.
|
To illustrate, here’s how the error can occur using an implicit conversion of a SMALLMONEY to a VARCHAR:
DECLARE @SmallMoney SMALLMONEY
DECLARE @VarChar VARCHAR(8)
SET @SmallMoney = 100000.00
SET @VarChar = @SmallMoney
Msg 292, Level 16, State 2, Line 5
There is insufficient result space to convert a smallmoney value to varchar.
The total number of characters needed to convert the SMALLMONEY to VARCHAR in this example is 9 and not 8 as the period has to be included as a character in the output.
Here’s another way of encountering the error using an explicit conversion from a SMALLMONEY to NVARCHAR using either the CAST or CONVERT functions:
DECLARE @SmallMoney SMALLMONEY
SET @SmallMoney = 100000.00
SELECT CAST(@SmallMoney AS NVARCHAR(8))
Msg 292, Level 16, State 2, Line 3
There is insufficient result space to convert a smallmoney value to nvarchar.
A similar way of getting the error is with negative SMALLMONEY values, as illustrated in the following:
DECLARE @SmallMoney SMALLMONEY
SET @SmallMoney = -100000.00
SELECT CAST(@SmallMoney AS VARCHAR(9))
Msg 292, Level 16, State 2, Line 3
There is insufficient result space to convert a smallmoney value to varchar.
Solution / Workaround:
This error can easily be avoided by making sure that the length of the VARCHAR or NVARCHAR value is long enough to accommodate the length of the SMALLMONEY value, taking into consideration the period and the negative sign in the case of negative values.
Using the first example above showing the implicit conversion, by increasing the length of the VARCHAR local variable to 9, the error will be avoided:
DECLARE @SmallMoney SMALLMONEY
DECLARE @VarChar VARCHAR(9)
SET @SmallMoney = 100000.00
SET @VarChar = @SmallMoney
Similarly, the error can be avoided in the explicit conversion example above by also increasing the length of the VARCHAR value to 9:
DECLARE @SmallMoney SMALLMONEY
SET @SmallMoney = 100000.00
SELECT CAST(@SmallMoney AS NVARCHAR(9)) AS [VarCharValue]
VarCharValue
--------------
100000.00
As for negative SMALLMONEY values, make sure the length of the VARCHAR value takes into consideration both the period and the negative sign. In the last example above, the length of the VARCHAR value should be set to 10 instead of just 9:
DECLARE @SmallMoney SMALLMONEY
SET @SmallMoney = -100000.00
SELECT CAST(@SmallMoney AS VARCHAR(10)) AS [NegativeSmallMoney]
NegativeSmallMoney
--------------------
-100000.00
|