|
|
Error Message
Server: Msg 183, Level 15, State 1, Line 1
The scale (<Scale Value>) for column '<Column Name>'
must be within the range 0 to <Precision Value>.
DECIMAL and NUMERIC are numeric data types that have fixed precision and scale. When maximum precision is used, which is 38, valid values are from -10^38 through 10^38 – 1. NUMERIC data type is functionally equivalent to DECIMAL data type. The syntax for declaring a local variable or a column as DECIMAL or NUMERIC data type is as follows:
|
DECIMAL ( p [, s] )
NUMERIC ( p [, s] )
Precision (p) is the maximum number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
The optional scale (s) is the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through the value of the precision (p). Scale can only be specified if precision is specified. The default scale is 0.
Given the definition of the precision and scale of a DECIMAL or NUMERIC data type, this error message will be encountered if the specified scale is greater than the precision when defining a DECIMAL or NUMERIC column in a table or table variable:
CREATE TABLE [dbo].[Product] (
[ProductID] INT,
[ProductName] VARCHAR(100),
[Width] DECIMAL(4, 6),
[Length] DECIMAL(4, 6),
[Height] DECIMAL(4, 6)
)
Msg 183, Level 15, State 1, Line 4
The scale (6) for column 'Width' must be within the range 0 to 4.
DECLARE @StoreLocation TABLE (
[StoreName] VARCHAR(50),
[StoreAddress] VARCHAR(100),
[Latitude] DECIMAL(2, 6),
[Longitude] DECIMAL(3, 6)
)
Msg 183, Level 15, State 1, Line 4
The scale (6) for column 'Latitude' must be within the range 0 to 2.
A different error message will be encountered when the scale is greater than the precision when defining a DECIMAL or NUMERIC local variable:
DECLARE @Pi DECIMAL(1, 6) -- 3.141592
Msg 192, Level 15, State 1, Line 1
The scale must be less than or equal to the precision.
DECLARE @Latitude DECIMAL(2, 6)
DECLARE @Longitue DECIMAL(3, 6)
Msg 192, Level 15, State 1, Line 1
The scale must be less than or equal to the precision.
Msg 192, Level 15, State 1, Line 2
The scale must be less than or equal to the precision.
Solution / Work Around
To avoid this error, as the error message suggests, the scale must be within the range of 0 and the value of the precision. This can easily be done by increasing the value of the precision to include the digits both before and after the decimal point.
Here’s an updated version of the scripts earlier that fixes the issue:
CREATE TABLE [dbo].[Product] (
[ProductID] INT,
[ProductName] VARCHAR(100),
[Width] DECIMAL(10, 6),
[Length] DECIMAL(10, 6),
[Height] DECIMAL(10, 6)
)
DECLARE @StoreLocation TABLE (
[StoreName] VARCHAR(50),
[StoreAddress] VARCHAR(100),
[Latitude] DECIMAL(8, 6),
[Longitude] DECIMAL(9, 6)
)
In the case of the incorrect scale in the definition of a local variable of DECIMAL or NUMERIC data type, simply increase the size of the precision to include the digits both before and after the decimal point.
DECLARE @Pi DECIMAL(7, 6) -- 3.141592
DECLARE @Latitude DECIMAL(8, 6) –- 2 Digits to the left and 6 digits to the right.
DECLARE @Longitue DECIMAL(9, 6) –- 3 Digits to the left and 6 digits to the right.
|