SA0124 : Columns in COALESCE are not all the same data type

The topic describes the SA0124 analysis rule.

Message

Columns in COALESCE are not all the same data type

Description

The rule checks and warns if COALESCE function arguments do not have same data type.

Note Note

The precision, scale and size of the data types are not considered in this version of the rule.
How to fix

Consider reviewing your code as unexpected results can occur if arguments with different data types are feed through a COALESCE.

Scope

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

Parameters
Name Description Default Value
OutputResolvedTypes

Outputs the types of the COALESCE parameters.

yes
IgnoreUnresolvedTypes

Specifies if to ignore an operator if one of the operand’s type cannot e resolved.

yes
Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE TABLE #wages
 2(
 3    emp_id        tinyint identity,
 4    hourly_wage   decimal   NULL,
 5    salary        money   NULL,
 6    commission    decimal(8,2)   NULL,
 7    num_sales     tinyint   NULL,
 8        info1                  nvarchar(123) NULL,
 9        info2                   varchar(122) NULL,
10);
11
12SELECT        COALESCE(hourly_wage * 40 * 52, 
13                             salary, 
14                                 commission * num_sales) AS 'Total Amount 1' ,
15                COALESCE(hourly_wage * 20 * 52,    
16                                 salary, 
17                                 commission * num_sales, 
18                                 commission , 
19                                 num_sales, 
20                                 info1,
21                                 info2,        
22                                 $41200.00,
23                                 '0.0000',  
24                                 '0') AS 'Total Amount 2' 
25FROM #wages
26ORDER BY 'Total Amount 1', 'Total Amount 2';
27
28DECLARE @Data xml 
29SET @data = EVENTDATA()
30
31SELECT coalesce(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'int') ,1);
32SELECT coalesce(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'char') ,1);
Analysis Results
  Message Line Column
1 SA0124 : Columns in COALESCE are not all the same data type.: COALESCE(*int,money,decimal,decimal,tinyint,nvarchar,varchar,*money,*char,*char) 15 2
2 SA0124 : Columns in COALESCE are not all the same data type.: COALESCE(char,*int) 32 7
See Also

Other Resources

Analysis Rules