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

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:

SQL
Copy
1-- Example of problematic code with repeated argument
2EXEC 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:

SQL
Copy
1-- Corrected example removing duplicated argument
2EXEC 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.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1Test.Greeting 'message', 'message'
 2
 3declare @Message nvarchar(100) = 'hi'
 4declare @Name nvarchar(10) = 'George'
 5
 6exec Test.Greeting  @param1 = @Message, @param2 = @Message
 7exec Test.Greeting  @Message, @Message
 8exec Test.Greeting  @Name, @Message, @Name 
 9
10select isnull(message,message) from dbo.Greetings
11select isnull(message,'hello') from dbo.Greetings
12select isnull(@Name, @Message, @Name ) from dbo.Greetings
13select dbo.myFunction(name,name,message ) from dbo.Greetings
14
15
16select 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
See Also

Other Resources

Analysis Rules