|
|
SQL Server Error Messages - Msg 237
Error Message
Server: Msg 237, Level 16, State 2, Line 1
There is insufficient result space to convert a money
value to bigint / int / smallint / tinyint.
This error occurs when converting, either implicitly or explicitly using the CAST or CONVERT system functions, a MONEY value to an INT, SMALLINT or TINYINT value but the MONEY value is larger than the maximum value the integer data type can accommodate.
|
To illustrate, here's how the error can occur using an implicit conversion of a MONEY data type to a SMALLINT data type:
DECLARE @Money MONEY
DECLARE @SmallInt SMALLINT
SET @Money = 1000000.00
SET @SmallInt = @Money
Msg 237, Level 16, State 2, Line 5
There is insufficient result space to convert a money value to smallint.
This error occurred because the maximum value a SMALLINT data type can accommodate is 32,767 and a value of 1,000,000 is being assigned to it in this example.
Here's another way of encountering this error using an explicit conversion from a MONEY data type to an INT data type using either the CAST or CONVERT system functions:
DECLARE @USNationalDebt MONEY
SET @USNationalDebt = 15699895378898.22
SELECT CAST(@USNationalDebt AS INT)
Msg 237, Level 16, State 1, Line 3
There is insufficient result space to convert a money value to int.
A similar way of getting this error is with negative MONEY values, as illustrated in the following, this time using the CONVERT system function:
DECLARE @WithdrawalAmount MONEY
SET @WithdrawalAmount = -1000000.00
SELECT CONVERT(TINYINT, @WithdrawalAmount)
Msg 237, Level 16, State 3, Line 3
There is insufficient result space to convert a money value to tinyint.
Solution / Work Around
This error can easily be avoided by using a data type that can accommodate the value of the MONEY data type.
Using the first example earlier using the implicit conversion, by changing the data type of the local variable from a SMALLINT to INT, the error will be avoided:
DECLARE @Money MONEY
DECLARE @Int INT
SET @Money = 1000000.00
SET @Int = @Money
The maximum value a SMALLINT data type can handle is only 32,767 while the maximum value an INT data type can handle is 2,147,483,647.
Similarly, the error can be avoided in the explicit conversion example earlier by changing the data type of local variable from an INT data type to a BIGINT data type:
DECLARE @USNationalDebt MONEY
SET @USNationalDebt = 15699895378898.22
SELECT CAST(@USNationalDebt AS BIGINT) AS [NationalDebt]
NationalDebt
-----------------
15699895378898
The maximum value an INT data type can handle is only 2,147,483,647 while the maximum value a BIGINT data type can handle is 9,223,372,036,854,775,807.
As for the negative MONEY value example earlier which converts the MONEY value to a TINYINT data type, by changing the data type to INT will avoid the problem because the smallest value a TINYINT data type can handle is 0. A SMALLINT data type cannot be used because the lowest value it can handle is only -32,768.
DECLARE @WithdrawalAmount MONEY
SET @WithdrawalAmount = -1000000.00
SELECT CONVERT(INT, @WithdrawalAmount) AS [WithdrawalAmount]
WithdrawalAmount
-------------------
-100000.00
|