Click or drag to resize

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.

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

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.

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:SA0132

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.

Categories

Design Rules

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

 MessageLineColumn
1SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(money,dbo.MyDataType)182
2SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(int,nvarchar)192
3SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(nvarchar,*int)262
4SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(char,*int)497
5SA0132 : The arguments of the ISNULL function are not of the same data type. IsNull(bit,*char)5312
See Also

Other Resources