SA0185 : Review the call for unintentionally passing the same value more than once as an argument

Introduction

Avoid repeating arguments in function or procedure calls to prevent logical errors and improve code clarity in SQL Server.

Description

Calling a function or procedure with the same value supplied multiple times as an argument can lead to logical errors and make the code unnecessarily complex. This problem is particularly relevant in SQL Server and other SQL RDBMS environments where precise argument handling is crucial for accurate data processing.

For example:

-- Example of problematic code with repeated argument
EXEC ExampleProcedure @Param1 = 10, @Param2 = 10;

This example is problematic because passing the same value multiple times may indicate a copy-and-paste error or misunderstood logic, potentially leading to incorrect outcomes or making it difficult to understand the intended logic.

It can lead to unintended side effects if the procedure or function is designed to handle arguments differently.
Code readability is reduced, making maintenance harder and increasing the risk of future bugs.

How to fix

Suppress the rule violation if the duplicated arguments are intentional and necessary. Otherwise, refactor your code to eliminate repeated arguments to prevent logical errors and improve code clarity.

Follow these steps to address the issue:

1.Review the function or procedure call and identify any arguments that are repeated unintentionally. Use CTRL+F in SQL Server Management Studio to locate repeated arguments.

2.If you find repeated arguments that are not needed, modify the procedure call to pass only unique arguments. Ensure each named parameter is given a distinct and intended value.

3.If the repeated arguments are intentional, add documentation or comments in the code to indicate this, and suppress the rule violation if necessary.

For example:

-- Corrected example removing duplicated argument
EXEC ExampleProcedure @Param1 = 10, @Param2 = 20;

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

Test.Greeting 'message', 'message'

declare @Message nvarchar(100) = 'hi'
declare @Name nvarchar(10) = 'George'

exec Test.Greeting  @param1 = @Message, @param2 = @Message
exec Test.Greeting  @Message, @Message
exec Test.Greeting  @Name, @Message, @Name

select isnull(message,message) from dbo.Greetings
select isnull(message,'hello') from dbo.Greetings
select isnull(@Name, @Message, @Name ) from dbo.Greetings
select dbo.myFunction(name,name,message ) from dbo.Greetings


select dbo.myFunction(1,2,10,1 ) from dbo.Greetings

Analysis Results

	Message	Line	Column
1	SA0185 : Value appears more than once in the procedure arguments.	1	14
2	SA0185 : Value appears more than once in the procedure arguments.	7	20
3	SA0185 : Value appears more than once in the procedure arguments.	8	20
4	SA0185 : Value appears more than once in the function arguments.	10	14
5	SA0185 : Value appears more than once in the function arguments.	12	14
6	SA0185 : Value appears more than once in the function arguments.	13	22
7	SA0185 : Value appears more than once in the function arguments.	16	22

Analysis Rules