SA0132 : The arguments of the ISNULL function are not of the same data type

The topic describes the SA0132 analysis rule.

Message

The arguments of the ISNULL function are not of the same data type

Description

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

SQL
Copy
1ISNULL ( check_expression , replacement_value )

Here is what the SQL Server Books Online say about this:

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

How to fix

Consider the possible truncation which may result when the second parameter of the function is implicitly converted to the type of the first parameter.

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 ISNULL 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
1 hour 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        decimal   NULL,
 6    commission    decimal   NULL,
 7    num_sales     tinyint   NULL,
 8    info1         nvarchar(123) NULL,
 9    info2         varchar(122) NULL,
10);
11
12SELECT
13                IsNull(40.1 * 52 , NULL),    
14                IsNull(1500.99, 1000),         
15                IsNull('0.0001', 'txt'),
16                IsNull('0.0001', N'0.0001'),
17                IsNull(1 * -12 , 1.222),
18                IsNull(cast('1111' as money),cast('1111' as dbo.MyDataType) + 12),
19                IsNull(convert(int, 10.23),convert(nvarchar(222), '10.23')),
20                IsNull(num_sales,hourly_wage),
21                IsNull(info1,info2) AS Info1,        
22                IsNull(info1,info1) AS Info2,        
23                IsNull(info2,info2) AS Info3,
24                IsNull(info1,info2 + N'12'),
25                IsNull(info1,info1 + N'12' + '11'),
26                IsNull(info1,info2 + N'12' + '11' + 3.14 + 11)
27FROM #wages ORDER BY 'Total Salary' 
28DROP TABLE #wages
29
30SELECT IsNull(USER,'UserName')
31SELECT IsNull(CURRENT_TIMESTAMP,getdate())
32SELECT IsNull(CURRENT_USER,'UserName')
33
34DECLARE @smallmoney smallmoney, @money money 
35DECLARE @bit bit
36SELECT TOP 1 
37                COALESCE(@smallmoney,@money,$125),
38                IsNull(@money,$125),
39                IsNull(@smallmoney,$125),
40                IsNull(@smallmoney,5),
41                IsNull(@bit,1)
42FROM sys.objects
43
44DECLARE @Data xml;
45
46SET @data = EVENTDATA()
47
48SELECT IsNull(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'int') ,1); 
49SELECT IsNull(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'char') ,1); 
50
51DECLARE @flag AS dbo.Flag
52SET @flag = IsNull(@flag,1)
53SET @flag = IsNull(@flag,'1')
Analysis Results
  Message Line Column
1 SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(money,dbo.MyDataType) 18 2
2 SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(int,nvarchar) 19 2
3 SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(nvarchar,*int) 26 2
4 SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(char,*int) 49 7
5 SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(bit,*char) 53 12
See Also

Other Resources

Analysis Rules