Skip to content

SA0187 : Duplicated string literals complicate the refactoring

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

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:

-- Example of problematic query with duplicated string literals
SELECT * FROM Users WHERE Country = 'USA';
SELECT * 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.

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:

DECLARE @Country NVARCHAR(50) = 'USA';
SELECT * FROM Users WHERE Country = @Country;
SELECT * FROM Customers WHERE Country = @Country;

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

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

The rule does not need Analysis Context or SQL Connection.

8 minutes per issue.

Design Rules, Code Smells

There is no additional info for this rule.

CREATE TABLE Test.Greeting
(
GreetingId INT IDENTITY (1,1) PRIMARY KEY,
Message nvarchar(255) NOT NULL,
)
INSERT INTO Test.Greeting (Message)
SELECT 'Hello!','Hello','Hello12','123','123','123','123','123'
UNION ALL
SELECT 'Hi!'
UNION ALL
SELECT 'Hello, world!'
INSERT INTO Test.Greeting (Message)
VALUES ('How do yo do?'),
('Good morning!'),
('Good night!')
DELETE Test.Greeting WHERE GreetingId = 3
SELECT * FROM Test.Greeting g
WHERE
g.Message like 'Hello%'
DROP TABLE Test.Greeting
  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

Analysis Rules