SA0240 : The stored procedure does not return result code

In SQL Server, stored procedures are designed to execute a series of statements and can optionally return a result code that indicates the success or failure of the execution. This result code provides valuable feedback about the procedure’s execution, especially in complex systems where multiple operations are interdependent.

For example:

1-- Example of a stored procedure without a result code
2CREATE PROCEDURE ExampleProcedure 
3AS
4BEGIN
5    -- Operation without any result feedback
6    UPDATE TableName SET ColumnName = 'Value';
7END

This code fragment lacks any mechanism to convey whether the update operation completed successfully, making error handling more difficult during execution. Ensuring your stored procedures return a result code can help determine the success or failure of an operation, enabling better error handling strategies.

• Without a result code, clients calling the procedure lack immediate feedback on its success or failure.

• Debugging and maintaining database applications become more challenging, as diagnostic information is limited.

`

Ensure stored procedures return a result code to indicate their execution status, facilitating better error handling and feedback mechanisms.

Follow these steps to address the issue:

1. Identify stored procedures that lack a RETURN statement to provide execution status feedback.

2. Modify the procedure to include a RETURN statement, which should be strategically placed to communicate success or failure. The RETURN value typically uses 0 for success and any non-zero value for different types of failure.

3. Test the updated procedure to ensure that the RETURN value correctly reflects the execution outcome.

For example:

 1-- Example of a stored procedure with a result code
 2CREATE PROCEDURE ExampleProcedure 
 3AS
 4BEGIN
 5    BEGIN TRY
 6        -- Simulate an operation
 7        UPDATE TableName SET ColumnName = 'Value';
 8        -- Return 0 indicating success
 9        RETURN 0;
10    END TRY
11    BEGIN CATCH
12        -- Return a non-zero value indicating failure
13        RETURN 1;
14    END CATCH
15END

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

There is no additional info for this rule.

Other Resources