SA0144 : The code following the RETURN or the RAISERROR statements will never be executed

Dead code in T-SQL scripts, which is never executed, can cause inefficiencies and increase confusion in code maintenance.

Description

The issue of dead code arises when parts of your T-SQL scripts are included but will never be executed, leading to inefficiencies and confusion.

In the context of T-SQL and SQL Server, dead code can occur in stored procedures, functions, and triggers. It often results from unconditional RETURN statements or RAISERROR commands with severity levels above 10, especially when placed inside BEGIN/END TRY blocks.

For example:

SQL
Copy
 1-- Example of dead code
 2CREATE PROCEDURE [dbo].[checkForDeadCode]
 3(
 4    @InputValue BIGINT
 5) 
 6AS 
 7IF (@InputValue = 0)
 8BEGIN
 9   RETURN 0;
10END
11ELSE
12BEGIN
13    RETURN @InputValue + 1;
14END
15RETURN -1;
16PRINT 'This message will never be printed';

This example illustrates a common issue where the PRINT statement is unreachable because the procedure always exits before reaching it. This kind of dead code can mislead developers and complicate maintenance.

  • Unreachable code segments can cause confusion during debugging and reviewing, leading to misunderstandings about the logic flow.

  • Including unnecessary code increases script complexity, which can negatively impact performance, especially in systems where maintaining efficiency is critical.

How to fix

Remove any dead code from your T-SQL scripts to optimize efficiency and clarity.

Follow these steps to address the issue:

  1. Review your T-SQL script to identify code segments that cannot be reached due to logical pathways, such as those interrupted by RETURN statements or RAISERROR commands with a severity level above 10 within BEGIN/END TRY blocks.

  2. Analyze each identified unreachable section to determine if they provide necessary logic or alerts that are being incorrectly bypassed.

  3. If the code is genuinely redundant and not required for execution, remove these segments to improve script maintainability and performance.

For example:

SQL
Copy
 1-- Before removing dead code
 2CREATE PROCEDURE [dbo].[checkForDeadCode]
 3(
 4    @InputValue BIGINT
 5) 
 6AS 
 7IF (@InputValue = 0)
 8BEGIN
 9   RETURN 0;
10END
11ELSE
12BEGIN
13    RETURN @InputValue + 1;
14END
15RETURN -1;
16PRINT 'This message will never be printed'; -- Dead code example

SQL
Copy
 1-- After removing dead code
 2CREATE PROCEDURE [dbo].[checkForDeadCode]
 3(
 4    @InputValue BIGINT
 5) 
 6AS 
 7IF (@InputValue = 0)
 8BEGIN
 9   RETURN 0;
10END
11ELSE
12BEGIN
13    RETURN @InputValue + 1;
14END
15-- Removed unreachable PRINT statement

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
20 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information
Example Test SQL
SQL
Copy
 1ALTER PROCEDURE [dbo].[test_DeadCode_Rule]
 2(
 3@Param1 INT = 0
 4) 
 5AS 
 6BEGIN
 7
 8        BEGIN TRY
 9                RAISERROR('@Param1 should not be null',11, 111);
10                PRINT 'dead code'
11        END TRY
12        BEGIN CATCH 
13                SET @Param1 = 1
14        END CATCH 
15
16        IF (@Param1 > 0)
17        BEGIN
18                RETURN 100/@Param1;
19                PRINT 'This statement will not be executed';
20        END
21        IF (@Param1 < 0)
22        BEGIN
23                RAISERROR ('Raise error instead of using RETURN statement.', 2,1);
24                PRINT 'This statement will not be executed';
25        END
26END
Analysis Results
  Message Line Column
1 SA0144 : The code following the RAISERROR statement will never be executed. 9 2
2 SA0144 : The code following the RETURN statement will never be executed. 18 2
See Also

Other Resources

Analysis Rules