SA0187 : Duplicated string literals complicate the refactoring

Repeated use of a single string literal can complicate refactoring and lead to errors.

Description

In T-SQL code, using the same string literal multiple times in your queries can create maintenance challenges. When you need to change the literal, every instance has to be found and updated. This is error-prone and can lead to overlooked or inconsistent values.

For example:

SQL
Copy
1-- Example of problematic query with duplicated string literals
2SELECT * FROM Users WHERE Country = 'USA';
3SELECT * FROM Customers WHERE Country = 'USA';

In this example, the country ‘USA’ is hardcoded in multiple places. If the literal needs to change to ‘United States’, every occurrence must be modified.

  • Refactoring is laborious and prone to human error, leading to potential bugs if any instance is missed.

  • String literals used in multiple places can result in inconsistency if updates are not uniformly applied.

How to fix

To improve maintainability and reduce errors, store repeated string literals in a variable and use this variable in your SQL queries.

Follow these steps to address the issue:

  1. Identify the string literals that are duplicated across your queries. In this scenario, look for instances like ‘USA’ used in multiple places.

  2. Declare a variable at the beginning of your script to store the string literal. For example, you can declare a variable @Country to hold the value ‘USA’.

  3. Replace all instances of the string literal in your queries with the variable. This ensures a single point of update if the literal changes in the future.

For example:

SQL
Copy
1DECLARE @Country NVARCHAR(50) = 'USA';
2
3SELECT * FROM Users WHERE Country = @Country;
4SELECT * FROM Customers WHERE Country = @Country;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
NumberOfDuplicatesThreshold

Maximum number of allowed duplicates of a string.

3
MinimalStringLength

Minimal length of a string that to be tested for duplicates.

3
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE Test.Greeting
 2(
 3GreetingId INT IDENTITY (1,1) PRIMARY KEY,
 4Message nvarchar(255) NOT NULL,
 5)
 6
 7INSERT INTO Test.Greeting (Message) 
 8SELECT 'Hello!','Hello','Hello12','123','123','123','123','123'
 9UNION ALL 
10SELECT 'Hi!'
11UNION ALL
12SELECT 'Hello, world!'
13
14INSERT INTO Test.Greeting (Message) 
15VALUES ('How do yo do?'),
16        ('Good morning!'),
17        ('Good night!')
18
19DELETE  Test.Greeting WHERE GreetingId = 3
20
21SELECT * FROM Test.Greeting g 
22WHERE 
23g.Message like 'Hello%'
24
25DROP TABLE Test.Greeting
Analysis Results
  Message Line Column
1 SA0187 : The string literal ‘123’ is duplicated 4 times in the current batch. Extract the string into a variable and use the variable instead. 8 34
See Also

Other Resources

Analysis Rules