Minor

Description

The "String literals should not be duplicated" code rule for SQL Server ensures that string literals are not used multiple times in a query or stored procedure. This rule helps to reduce code complexity, as well as improve readability and maintainability of the code. It also helps to minimize the amount of code that needs to be written, as well as reduce the chances of errors being introduced as a result of duplicate strings.

For example, if a query contains the same string literal multiple times, it can be replaced with a single reference. This can help to improve the performance of the query, as well as reduce the amount of code that needs to be written and maintained.

Key Benefits

• Reduced Code Complexity: By avoiding duplicating string literals, the code will be more concise and easier to read.
• Improved Performance: By avoiding duplicating string literals, the code will be more efficient and perform better.
• Reduced Maintenance: By avoiding duplicating string literals, the code will be easier to maintain.

Non-compliant Code Example

DECLARE @Number int;
SET @Number = 50;  
IF @Number > 100  AND @Number < 200      
    PRINT 'The number is large.';   --Non compliant code (String literals are duplicated)
ELSE 
    BEGIN
        IF @Number < 10  
            PRINT 'The number is small.';   --Non compliant code (String literals are duplicated)
        ELSE  
            PRINT 'The number is medium.';   --Non compliant code (String literals are duplicated)
    END ;  

IF @Number > 200      
    PRINT 'The number is large.';   --Non compliant code (String literals are duplicated)

IF @Number = 0  
    PRINT 'The number is small.';   --Non compliant code (String literals are duplicated)
ELSE  
    PRINT 'The number is medium.';   --Non compliant code (String literals are duplicated)
GO 

Compliant Code Example

Declare @SmallMsg varchar(200) = 'The number is small.';
Declare @MediumMsg varchar(200) = 'The number is medium.';
Declare @LargeMsg varchar(200) = 'The number is large.';

DECLARE @Number int;
SET @Number = 50;  
IF @Number > 100  AND @Number < 200      
    PRINT @LargeMsg;   --Compliant code (String literals declared above and used by variable at all locations)
ELSE 
    BEGIN
        IF @Number < 10  
            PRINT @SmallMsg;   --Compliant code
        ELSE  
            PRINT @MediumMsg ;   --Compliant code
    END ;  

IF @Number > 200      
    PRINT @LargeMsg;   --Compliant code

IF @Number = 0  
    PRINT @SmallMsg;   --Compliant code
ELSE  
    PRINT @MediumMsg ;   --Compliant code
GO