Skip to content

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

Ensure compatibility and prevent issues with parameter values in T-SQL stored procedures.

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 call
EXEC 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.

`

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 call
DECLARE @CorrectNumericParam INT = 123;
EXEC dbo.MyProcedure @NumericParam = @CorrectNumericParam;

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

Name Description Default Value
IgnoreNumericDecimalScaleTruncations Ignore data loss warning when source has higher scale than the scale of the target. no

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

20 minutes per issue.

Design Rules

Data type conversion (Database Engine)

Data type precedence (Transact-SQL)

Precision, scale, and length

DECLARE @RC int
EXECUTE @RC = [HumanResources].[uspUpdateEmployeePersonalInfo]
@BusinessEntityID = 0.12e4
,@NationalIDNumber = '0123456789abcdef'
,@BirthDate = 0x00012
,@MaritalStatus = 'single'
,@Gender = N'123'
  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

Analysis Rules