Click or drag to resize

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.

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

Note Note

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

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0124

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.

Categories

Design Rules

Additional Information
Example Test Script
SQL
 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

 MessageLineColumn
1SA0124 : Columns in COALESCE are not all the same data type.: COALESCE(*int,money,decimal,decimal,tinyint,nvarchar,varchar,*money,*char,*char)152
2SA0124 : Columns in COALESCE are not all the same data type.: COALESCE(char,*int)327
See Also

Other Resources