SA0276 : The type of the assigned value is not compatible with the variable’s declared type

Type mismatches in variable assignments can lead to data integrity issues and unexpected runtime errors in SQL Server.

Description

When values assigned to variables or columns do not match their declared data types, it can create significant problems in SQL Server environments. These mismatches may cause data truncation, precision loss, or even runtime errors, leading to unpredictable behavior during query execution.

For example:

SQL
Copy
1-- Potentially problematic assignment
2DECLARE @variable CHAR(5);
3SET @variable = 'Hello World';
4
5-- Incompatible data type assignment
6DECLARE @amount DECIMAL(8,2);
7SET @amount = 12345.67890;

In the first example, assigning a VARCHAR(10) value ‘Hello World’ to a CHAR(5) variable results in truncation, losing ‘ World’.

In the second example, storing a DECIMAL(10,5) value in a DECIMAL(8,2) column leads to precision loss, altering the value to 12345.68.

  • Data truncation or precision loss can alter user data and calculations, leading to inaccurate results.

  • Type mismatches may not be evident during testing but can cause runtime errors and data issues in production.

`

How to fix

Ensure type compatibility between assigned values and variable declarations to avoid conversion errors, data truncation, or precision loss.

Follow these steps to address the issue:

  1. Review the variable declarations to confirm that they match the expected data type, range, precision, and scale required for the values being assigned. For example, if the expected value exceeds the current variable type capabilities, adjust the variable declaration accordingly.

  2. Use explicit casting or conversion functions such as CAST or CONVERT to align the value type with the variable’s declared type. This allows controlled and predictable type conversions.

  3. Avoid implicit conversions by ensuring data types are consistent across variables, literals, and expressions. Consistency prevents unintended type conversion issues and maintains data integrity.

For example:

SQL
Copy
1-- Corrected variable declaration with compatible length
2DECLARE @variable VARCHAR(11);
3SET @variable = 'Hello World';
4
5-- Conversion to prevent precision loss
6DECLARE @amount DECIMAL(10,5);
7SET @amount = CAST(12345.67890 AS DECIMAL(10,5));

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
IgnoreNumericDecimalScaleTruncations

Ignore data loss warning when source has higher scale than the scale of the target.

no
Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix
20 minutes per issue.
Categories

Design Rules

Additional Information
Example Test SQL
SQL
Copy
 1create procedure TestSA0276.ProcedureTestCase2
 2 @var varchar(5) = '123456' -- Varchar truncated
 3as
 4
 5---Variable assignment
 6declare 
 7 @dc decimal(5, 2) = 00123.4500,
 8 @dcs decimal(5, 2) = 112.341100,
 9 @dt int = 123.5; -- Scale truncated
10
11declare @var2 varchar(4), -- Smaller varchar
12 @dc2 decimal(5, 1),      -- Decimal with smaller scale
13 @dcs2 decimal(4, 2);     -- Decimal with smaller precision
14
15select @var2 = @var,      -- Varchar truncated
16 @dc2 = @dc,              -- Scale truncated
17 @dcs2 = @dcs;            -- When @dcs is 123.45 - "Arithmetic overflow error converting numeric to data type numeric."
18
19set @var2 = N'123456';    -- Varchar truncated
20set @dc2 = 123.45;        -- Scale truncated
21set @dcs2 = 123.34;       -- "Arithmetic overflow error converting numeric to data type numeric."
22set @dt = 'NAN'
Analysis Results
  Message Line Column
1 SA0276 : Possible truncation of value assigned to variable @var – (varchar(6) to varchar(5)). 2 17
2 SA0276 : Possible truncation of value assigned to variable @dcs – (decimal(7,4) to decimal(5,2)). 8 20
3 SA0276 : Implicit conversion will occur when assigning value to variable @dt – (float to int). 9 9
4 SA0276 : Possible truncation of value assigned to variable @var2 – (varchar(5) to varchar(4)). 15 13
5 SA0276 : Possible truncation of value assigned to variable @dc2 – (decimal(5,2) to decimal(5,1)). 16 6
6 SA0276 : Possible truncation of value assigned to variable @dcs2 – (decimal(5,2) to decimal(4,2)). 17 7
7 SA0276 : Possible truncation of value assigned to variable @var2 – (varchar(6) to varchar(4)). 19 10
8 SA0276 : Possible truncation of value assigned to variable @dc2 – (decimal(5,2) to decimal(5,1)). 20 9
9 SA0276 : Possible truncation of value assigned to variable @dcs2 – (decimal(5,2) to decimal(4,2)). 21 10
10 SA0276 : Implicit conversion will occur when assigning value to variable @dt – (char(3) to int). 22 8
See Also

Other Resources

Analysis Rules