SA0139 : The procedure argument type is not compatible with the procedure parameter type

The topic describes the SA0139 analysis rule.

Message

The procedure argument type is not compatible with the procedure parameter type

Description

The rule checks T-SQL code for stored procedures called with parameter values, which are not compatible, will require conversion or may cause argument value truncation.

It is recommended to use compatible parameters in order to avoid conversion and data truncation.

Scope

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

Parameters

Rule has no parameters.

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
 1DECLARE @RC int
 2DECLARE @BusinessEntityID int
 3DECLARE @NationalIDNumber nvarchar(15)
 4DECLARE @BirthDate datetime
 5DECLARE @MaritalStatus nchar(1)
 6DECLARE @Gender nchar(1)
 7
 8EXECUTE @RC = [HumanResources].[uspUpdateEmployeePersonalInfo] 
 9   @BusinessEntityID
10  ,@NationalIDNumber
11  ,@BirthDate
12  ,@MaritalStatus
13  ,@Gender
14
15EXECUTE @RC = [HumanResources].[uspUpdateEmployeePersonalInfo] 
16   @BusinessEntityID = 0.12e4
17  ,@NationalIDNumber = '1234567890123456'
18  ,@BirthDate = 0x00012
19  ,@MaritalStatus = 'single'
20  ,@Gender = 123
21
22DECLARE @uniqueidentifier uniqueidentifier
23DECLARE @varbinary varbinary(15)
24DECLARE @varchar varchar(15)
25DECLARE @nvarchar varchar(15)
26DECLARE @char char(2)
27DECLARE @bit bit
28DECLARE @datetime datetime
29DECLARE @bigint bigint
30DECLARE @smallint smallint
31DECLARE @float float
32DECLARE @real real
33DECLARE @money money
34DECLARE @numeric decimal(10,4)
35DECLARE @text text
36DECLARE @image image
37DECLARE @sql_variant sql_variant
38DECLARE @xml xml
39
40EXECUTE @RC = [HumanResources].[uspUpdateEmployeePersonalInfo] 
41   @BusinessEntityID = @smallint
42  ,@NationalIDNumber = @text
43  ,@BirthDate = @sql_variant
44  ,@MaritalStatus = @varbinary
45  ,@Gender = @char
46
47EXECUTE @RC = [HumanResources].[uspUpdateEmployeePersonalInfo] 
48   @BusinessEntityID = @datetime
49  ,@NationalIDNumber = @image
50  ,@BirthDate = @bigint
51  ,@MaritalStatus = @bigint
52  ,@Gender = @xml

Analysis Results

  Message Line Column
1 SA0139 : Parameter @BusinessEntityID value implicit conversion will occure – (float to int). 16 21
2 SA0139 : Parameter @NationalIDNumber value possible truncation – (varchar(16) to nvarchar(15)). 17 21
3 SA0139 : Parameter @BirthDate value implicit conversion will occure – (binary to datetime). 18 14
4 SA0139 : Parameter @MaritalStatus value possible truncation – (varchar(6) to nchar(1)). 19 18
5 SA0139 : Parameter @Gender value implicit conversion will occure – (int to nchar). 20 11
6 SA0139 : Parameter @NationalIDNumber value implicit conversion will occure – (text to nvarchar). 42 21
7 SA0139 : Parameter @BirthDate value explicit conversion is required – (sql_variant to datetime). 43 14
8 SA0139 : Parameter @MaritalStatus value implicit conversion will occure – (varbinary to nchar). 44 18
9 SA0139 : Parameter @Gender value possible truncation – (char(2) to nchar(1)). 45 11
10 SA0139 : Parameter @BusinessEntityID value conversion is not allowed – (datetime to int). 48 21
13 SA0139 : Parameter @MaritalStatus value implicit conversion will occure – (bigint to nchar). 51 18
14 SA0139 : Parameter @Gender value explicit conversion is required – (xml to nchar). 52 11
See Also

Other Resources