SA0248 : Stored procedure called with mixing both unnamed and named arguments style

Introduction

Mixing named and unnamed arguments in stored procedure calls can lead to confusion and errors.

Description

When calling a stored procedure, arguments can be specified either by name or by their order. However, combining these two methods in a single call can cause unexpected behavior and reduce code readability.

For example:

-- Example of problematic stored procedure call
EXEC dbo.ProcedureName @Param2 = 'value', 'value2';

In this example, mixing named and unnamed arguments can make it unclear which parameter is receiving the second value. As a result, maintaining the code becomes more challenging and error-prone.

It may result in incorrect data being passed to stored procedure parameters, causing logic errors.
The code is harder to understand and maintain, especially for developers unfamiliar with the procedure’s parameter list.

How to fix

Ensure consistency and clarity in stored procedure calls by using either all named arguments or all unnamed arguments.

Follow these steps to address the issue:

1.Identify the stored procedure call that mixes named and unnamed arguments. For example:

        EXEC dbo.ProcedureName @Param2 = 'value', 'value2';

2.Determine the preferred method for specifying arguments: either all named or all unnamed. Base this decision on team conventions or specific context needs.

3.If using named arguments, specify all parameters by their names. This enhances readability and reduces error potential. Adjust the example as follows:

        EXEC dbo.ProcedureName @Param1 = 'value1', @Param2 = 'value2';

4.If using unnamed arguments, ensure you correctly order all parameters according to the stored procedure definition. Adjust the example as follows:

        EXEC dbo.ProcedureName 'value1', 'value2';

For example:

-- Example of revised and consistent stored procedure call
EXEC dbo.ProcedureName @Param1 = 'value1', @Param2 = 'value2';

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

8 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 output,'20050225', 1223 ;
EXEC @result = uspGetWhereUsedProductID
EXEC TestReturnPlanForEX0018_Encrypted_Numbered;3 123

Analysis Results

	Message	Line	Column
1	SA0248 : Stored procedure called with mixing both unnamed and named arguments style.	3	5

Analysis Rules