The 'XXXXX (procedure name)' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead

I created one simple SP it is created successfully. There is no error with the SP it is working fine but it returns below message.

The 'XXXXX (procedure name)' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead

My sp was as below.

CREATE PROCEDURE [dbo].[P_Sql_Server_log_Audit] AS BEGIN declare @returnval int; BEGIN TRY                 insert into Sql_Server_log_Audit (User_Nm,Start_Time)                 values (SYSTEM_USER,CURRENT_TIMESTAMP) END TRY BEGIN CATCH SET @returnval=-1; END CATCH; Return  @returnval; END

This is simple SP which inset a record into the sql_server_log_audit custom table.

Before executing this SP see the records in the table.

 

Now I am executing this SP.

After execution 1 record is inserted successfully. Also, we are getting this message.

After analyzing this SP and fund that this message is due to only one reason – we have declared a variable inside the scope of the Stored Procedure and we have assigned the return value into the catch block. It means if any error occurs during inserting a record into the table, I have assign return value to -1.

In the try block, I did not assign value to the same procedure before we return the same variable. When any variable is not assigned any value it is by default assumed as a NULL. By Design stored procedures are not allowed to return the NULL value. Hence the value is automatically converted to zero (0) and returned.

We need to set the return value after declaring this variable. Now I am altering this SP and setting the default return value.

Records in the table.

 

Now I am executing this SP.

 

Message went. One new record inserted successfully.

See the records in the table.