SA0250 : Consider calling procedures with named arguments

Introduction

Ensure stored procedure calls use named arguments to improve code maintainability and prevent errors.

Description

When calling stored procedures in T-SQL code, it’s possible to use unnamed arguments by simply providing values in order. However, using unnamed arguments can lead to errors if the procedure is modified. This is because if new parameters are added to the procedure, especially in between existing ones, the execution order of unnamed arguments can cause unexpected behavior and runtime errors.

For example:

-- Example of problematic query with unnamed arguments
EXEC dbo.MyProcedure 'Value1', 'Value2';

In this example, if the stored procedure dbo.MyProcedure is altered to include a new parameter before the existing ones, the call could apply ‘Value1’ and ‘Value2’ to incorrect parameters, leading to incorrect processing or errors.

Arguments become mismatched if the procedure definition changes.
Harder to understand which value corresponds to which parameter without inspecting the stored procedure definition.

How to fix

To ensure code maintainability and prevent errors when executing stored procedures, use named arguments for each parameter.

Follow these steps to address the issue:

1.Identify all stored procedure calls in your code where unnamed arguments are used. These calls will typically appear as EXEC ProcedureName 'Value1', 'Value2' .

2.Replace each unnamed argument with a named argument by specifying the parameter name followed by the value. This ensures clarity and positional independence. For example, change 'Value1' to @ParameterName = 'Value1' .

3.Test the stored procedure calls to ensure that they execute correctly and that each parameter receives the appropriate value.

For example:

-- Example of corrected query with named arguments
EXEC dbo.MyProcedure @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

5 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	SA0250 : Consider calling procedures with named arguments.	2	15
2	SA0250 : Consider calling procedures with named arguments.	3	5
3	SA0250 : Consider calling procedures with named arguments.	10	15
4	SA0250 : Consider calling procedures with named arguments.	12	15
5	SA0250 : Consider calling procedures with named arguments.	14	5

Analysis Rules