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

Introduction

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:

-- Potentially problematic assignment
DECLARE @variable CHAR(5);
SET @variable = 'Hello World';

-- Incompatible data type assignment
DECLARE @amount DECIMAL(8,2);
SET @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:

-- Corrected variable declaration with compatible length
DECLARE @variable VARCHAR(11);
SET @variable = 'Hello World';

-- Conversion to prevent precision loss
DECLARE @amount DECIMAL(10,5);
SET @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.

Additional Information

Data type conversion (Database Engine)

Data type precedence (Transact-SQL)

Precision, scale, and length

Example Test SQL

create procedure TestSA0276.ProcedureTestCase2
 @var varchar(5) = '123456' -- Varchar truncated
as

---Variable assignment
declare
 @dc decimal(5, 2) = 00123.4500,
 @dcs decimal(5, 2) = 112.341100,
 @dt int = 123.5; -- Scale truncated

declare @var2 varchar(4), -- Smaller varchar
 @dc2 decimal(5, 1),      -- Decimal with smaller scale
 @dcs2 decimal(4, 2);     -- Decimal with smaller precision

select @var2 = @var,      -- Varchar truncated
 @dc2 = @dc,              -- Scale truncated
 @dcs2 = @dcs;            -- When @dcs is 123.45 - "Arithmetic overflow error converting numeric to data type numeric."

set @var2 = N'123456';    -- Varchar truncated
set @dc2 = 123.45;        -- Scale truncated
set @dcs2 = 123.34;       -- "Arithmetic overflow error converting numeric to data type numeric."
set @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 - (decimal(4,1) 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

Analysis Rules