SA0250 : Consider calling procedures with named arguments

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:

SQL
Copy
1-- Example of problematic query with unnamed arguments
2EXEC 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:

SQL
Copy
1-- Example of corrected query with named arguments
2EXEC 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.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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 output,'20050225', 1223 ;
13EXEC @result = uspGetWhereUsedProductID
14EXEC 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
See Also

Other Resources

Analysis Rules