SA0187 : Duplicated string literals complicate the refactoring
Introduction
Section titled “Introduction”Repeated use of a single string literal can complicate refactoring and lead to errors.
Description
Section titled “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:
-- Example of problematic query with duplicated string literalsSELECT * 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.
How to fix
Section titled “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:
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.
Parameters
Section titled “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
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”8 minutes per issue.
Categories
Section titled “Categories”Design Rules, Code Smells
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”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 ALLSELECT 'Hi!'UNION ALLSELECT '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 gWHEREg.Message like 'Hello%'
DROP TABLE Test.GreetingAnalysis Results
Section titled “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 |