Click or drag to resize

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, New 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

 MessageLineColumn
1SA0139 : Parameter @BusinessEntityID value implicit conversion will occure - (float to int).1621
2SA0139 : Parameter @NationalIDNumber value possible truncation - (varchar(16) to nvarchar(15)).1721
3SA0139 : Parameter @BirthDate value implicit conversion will occure - (binary to datetime).1814
4SA0139 : Parameter @MaritalStatus value possible truncation - (varchar(6) to nchar(1)).1918
5SA0139 : Parameter @Gender value implicit conversion will occure - (int to nchar).2011
6SA0139 : Parameter @NationalIDNumber value implicit conversion will occure - (text to nvarchar).4221
7SA0139 : Parameter @BirthDate value explicit conversion is required - (sql_variant to datetime).4314
8SA0139 : Parameter @MaritalStatus value implicit conversion will occure - (varbinary to nchar).4418
9SA0139 : Parameter @Gender value possible truncation - (char(2) to nchar(1)).4511
10SA0139 : Parameter @BusinessEntityID value conversion is not allowed - (datetime to int).4821
...
13SA0139 : Parameter @MaritalStatus value implicit conversion will occure - (bigint to nchar).5118
14SA0139 : Parameter @Gender value explicit conversion is required - (xml to nchar).5211
See Also

Other Resources