SQL SERVER – The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Here is one of the very common question I receive on SQLAuthority Facebook page. I usually answer them on Facebook but this one I find it very interesting so I decided to answer here.

“There are few of the stored procedure when I try to execute they return following message. Would you please explain what does it mean and is it alright to receive them?

Very interesting question and before I explore it further let us answer execute following stored procedure.

-- First Stored Procedure
CREATE PROCEDURE TestSP
AS
DECLARE @MyFirstParam INT
-- Additional Code
RETURN (@MyFirstParam)
GO

Now execute the stored procedure with following code.

EXEC TestSP
GO

When you execute the stored procedure it will return following message:

The ‘TestSP’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

If you notice this message, it is clear 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 no 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 can verify above logic by running the following code:

DECLARE @TestVar INT
EXEC @TestVar = TestSP
SELECT @TestVar TestVar
GO

You can see that when you run above code, instead of returning NULL value we get value of Zero (0) and in the message field we get above message.

Alternate Code

To avoid this situation you can modify above stored procedure to not return the value null. You can use ISNULL function before returning the values back from the stored procedure.

CREATE PROCEDURE TestSP
AS
DECLARE @MyFirstParam INT
-- Additional Code
RETURN (ISNULL(@MyFirstParam,0))
GO

Above change in the code will automatically covert the NULL to Zero (0) and it will not show the above message.

Which one is better?

Now the question is which method is better – to use ISNULL and covert it to Zero or let SQL handle this and message show up. Personally I prefer to use ISNULL as that gives me clear idea and there is no ambiguity in my code. I will not have to write additional code to handle NULL when it shows up.

What is your opinion? Please share in the comment area.

Reference: Pinal Dave (https://blog.sqlauthority.com)