SA0246 : Stored procedure executed with incorrect arguments

The topic describes the SA0246 analysis rule.

Message

Stored procedure executed with incorrect arguments

Description

The rule checks T-SQL code for stored procedures called with incorrect or missing required arguments.

These are the cases when an invalid argument rule violation is reported:

a. The procedure does not have such parameter.

b. The procedure is called with argument as OUTPUT, but parameter is not declared as such.

c. The procedure parameter is declared as OUTPUT, but is not specified as such.

d. The procedure parameter is specified more than once in the procedure call

e. The procedure parameter, which has no default value specified is not supplied.

How to fix

Fix the arguments of the stored procedure’s execute statement according the issue reported by the rule violation:

a. Remove the argument.

b. Either declare the parameter as an OUTPUT parameter or remove the OUTPUT form the argument.

c. Either remove OUTPUT from the parameter declaration or add OUTPUT the argument. The procedure parameter is declared as OUTPUT, but is not specified as such.

d. Review the procedure call and remove the duplicate named arguments.

e. Provide value for argument of the procedure parameter, which has no default value specified.

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.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1DECLARE  @result INT
 2EXEC @result = uspGetWhereUsedProductID  819, '20050225';
 3EXEC uspGetWhereUsedProductID;1   123, @CheckDate = '20050225', @StartProductID = 819;
 4
 5EXEC uspGetWhereUsedProductID   @NoSuchParam = 123, @CheckDate = '20050225', @StartProductID = 819;
 6
 7EXECUTE  AdventureWorks2008R2_Test.dbo.uspGetWhereUsedProductID  @StartProductID = 819, @CheckDate = '20050225';
 8
 9
10EXEC @result = uspGetWhereUsedProductID  819, '20050225', 122;
11DECLARE @output int
12EXEC @result = uspGetWhereUsedProductID  819, @output out,'20050225', 1223 ;
13EXEC @result = uspGetWhereUsedProductID
14EXEC 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
See Also

Other Resources