SA0246 : Stored procedure executed with incorrect arguments

Introduction

Incorrect or missing arguments in stored procedure calls can lead to errors in SQL Server, impacting application functionality and data processing.

Description

Incorrectly calling stored procedures can lead to errors in SQL Server, hindering application functionality and data processing. This issue is critical as SQL Server expects precise argument specifications for successful execution of stored procedures.

For example:

-- Example of problematic query
EXEC MyProcedure @Param1 = 'Value1', @Param2 = 'Value2', @Param3 OUTPUT;

This example could be problematic if @Param3 is not declared as OUTPUT in the stored procedure definition, or @Param2 is omitted and does not have a default value.

Calling a stored procedure with a parameter that doesn’t exist can cause execution failures.
Specifying a parameter as OUTPUT without matching procedure declaration causes runtime errors.
Omitting required parameters without default values results in incomplete procedure calls.
Specifying the same parameter multiple times can lead to ambiguous intent and unexpected results.

How to fix

Fix issues related to calling stored procedures in T-SQL code by addressing incorrect or missing arguments to ensure successful execution and application functionality.

Follow these steps to address the issue:

1.Remove any unnecessary arguments from the EXEC statement.

2.If the procedure parameter definition includes an OUTPUT specification, ensure that the corresponding argument in the call statement also has OUTPUT specified. If not needed, remove OUTPUT from the call.

3.Ensure that parameters specified as OUTPUT in the stored procedure declaration are matched with corresponding OUTPUT keyword in the EXEC statement.

4.Eliminate any duplicate named arguments in the stored procedure call.

5.Provide values for all required parameters that do not have default values in the stored procedure definition.

For example:

-- Example of corrected query
EXEC MyProcedure @Param1 = 'Value1', @Param2 = 'Value2', @Param3 = @OutputVariable OUTPUT;

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.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

DECLARE  @result INT
EXEC @result = uspGetWhereUsedProductID  819, '20050225';
EXEC uspGetWhereUsedProductID;1   123, @CheckDate = '20050225', @StartProductID = 819;

EXEC uspGetWhereUsedProductID   @NoSuchParam = 123, @CheckDate = '20050225', @StartProductID = 819;

EXECUTE  AdventureWorks2008R2_Test.dbo.uspGetWhereUsedProductID  @StartProductID = 819, @CheckDate = '20050225';


EXEC @result = uspGetWhereUsedProductID  819, '20050225', 122;
DECLARE @output int
EXEC @result = uspGetWhereUsedProductID  819, @output out,'20050225', 1223 ;
EXEC @result = uspGetWhereUsedProductID
EXEC TestReturnPlanForEX0018_Encrypted_Numbered;3 123

Analysis Results

	Message	Line	Column
1	SA0246 : The procedure parameter is specified more than once in the procedure call.	3	34
2	SA0246 : The procedure does not have such parameter.	5	45
3	SA0246 : The procedure does not have such parameter.	10	58
4	SA0246 : The procedure is called with parameter as OUTPUT, but parameter is not declared as such.	12	46
5	SA0246 : The procedure does not have such parameter.	12	58
6	SA0246 : The procedure does not have such parameter.	12	70
7	SA0246 : Procedure expects parameter @StartProductID, which was not supplied.	13	15
8	SA0246 : Procedure expects parameter @CheckDate, which was not supplied.	13	15
9	SA0246 : The procedure does not have such parameter.	14	50

Analysis Rules