SA0139 : The procedure argument type is not compatible with the procedure parameter type
Introduction
Section titled “Introduction”Ensure compatibility and prevent issues with parameter values in T-SQL stored procedures.
Description
Section titled “Description”In T-SQL code, stored procedures are often called with parameters that must match the expected data types. If a parameter is provided with an incompatible data type, SQL Server may attempt to convert it, which can lead to performance overhead, unexpected results, or even value truncation. Ensuring the correct data type alignment between the provided arguments and the stored procedure’s parameters is crucial.
For example:
-- Example of a problematic stored procedure callEXEC dbo.MyProcedure @NumericParam = '123abc';In this example, if @NumericParam is expected to be an integer, SQL Server will attempt to convert the string '123abc' . This conversion can fail or result in truncation, leading to unexpected behavior and errors.
-
Increased risk of runtime errors due to failed conversions.
-
Potential performance degradation from unnecessary conversion operations.
-
Loss of data precision or unintended truncation of values.
`
How to fix
Section titled “How to fix”Ensure compatibility between parameter and argument types in stored procedure calls to avoid conversion errors and data truncation.
Follow these steps to address the issue:
1.Review the stored procedure definition to identify the expected data types for each parameter. Use sp_helptext to view the procedure’s script.
2.Verify that each argument provided in the call to the stored procedure matches its corresponding parameter’s expected data type. Adjust the argument accordingly if there is a mismatch.
3.If needed, explicitly convert the argument to the correct data type using T-SQL conversion functions like CAST or CONVERT .
For example:
-- Corrected stored procedure callDECLARE @CorrectNumericParam INT = 123;EXEC dbo.MyProcedure @NumericParam = @CorrectNumericParam;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| IgnoreNumericDecimalScaleTruncations | Ignore data loss warning when source has higher scale than the scale of the target. | no |
Remarks
Section titled “Remarks”The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Design Rules
Additional Information
Section titled “Additional Information”Data type conversion (Database Engine)
Data type precedence (Transact-SQL)
Example Test SQL
Section titled “Example Test SQL”DECLARE @RC int
EXECUTE @RC = [HumanResources].[uspUpdateEmployeePersonalInfo] @BusinessEntityID = 0.12e4 ,@NationalIDNumber = '0123456789abcdef' ,@BirthDate = 0x00012 ,@MaritalStatus = 'single' ,@Gender = N'123'Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0139 : Parameter @BusinessEntityID value implicit conversion will occur - (float to int). | 4 | 21 |
| 2 | SA0139 : Parameter @NationalIDNumber value possible truncation - (nchar(16) to nvarchar(15)). | 5 | 21 |
| 3 | SA0139 : Parameter @BirthDate value implicit conversion will occur - (binary(2) to datetime). | 6 | 14 |
| 4 | SA0139 : Parameter @MaritalStatus value possible truncation - (nchar(6) to nchar(1)). | 7 | 18 |
| 5 | SA0139 : Parameter @Gender value possible truncation - (nchar(3) to nchar(1)). | 8 | 11 |