SA0153 : Always specify parameter names when calling stored procedures

Introduction

Using positional parameters instead of named parameters in stored procedure calls can lead to various issues.

Description

SQL Server allows calling stored procedures by specifying parameters either by name or by position. Relying solely on positional parameters can be problematic, especially when stored procedures are modified or when the order of parameters is unclear. Named parameters improve code readability and reduce errors when changes occur in the procedure’s parameter list.

For example:

-- Example of problematic stored procedure call
EXEC dbo.ProcedureName @Param1, @Param2;

This call uses positional parameters, which can be error-prone if the order of parameters in dbo.ProcedureName changes. Named parameters make it clear what each argument corresponds to.

Changes in stored procedure parameter order can easily lead to incorrect execution results.
Using named parameters enhances code readability and maintainability.

How to fix

Ensure that stored procedure calls use named parameters to enhance readability and maintainability, and to prevent errors due to changes in parameter order.

Follow these steps to address the issue:

1.Identify stored procedure calls within your code that use positional parameters. Positional parameters are used when values are provided without specifying parameter names, like @Param1, @Param2 .

2.Modify each discovered call to use named parameters. Specify each parameter name followed by its value using the = operator. For example, replace positional usage with @ParameterName = Value .

3.Review the modified stored procedure calls to ensure they conform to the expected parameter names and values as defined in the stored procedure’s declaration.

4.Test the stored procedure calls to verify that they function correctly and return expected results after the modifications.

For example:

-- Corrected stored procedure call using named parameters
EXEC dbo.ProcedureName @FirstParameter = @Param1, @SecondParameter = @Param2;

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

Specify Parameters

Example Test SQL

USE AdventureWorks2012;

-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';

-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;

-- Try to use a function as a parameter value.
-- This produces an error message.
--EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();

-- Passing the function value as a variable.

SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;


EXECUTE my_proc @second = 2, @first = 1, @third = 3;

exec my_proc @second = 2, @first = 1, @third = 3;

Analysis Results

	Message	Line
1	SA0153 : Always specify parameter names when calling stored procedures.	4
2	SA0153 : Always specify parameter names when calling stored procedures.	10
3	SA0153 : Always specify parameter names when calling stored procedures.	19

Analysis Rules