Case Procedure Error

Question

I have a stored procedure, for example below:

SELECT @AdminApproved = CASE WHEN @AdminApprovalDate IS NULL THEN 0 ELSE 1 END 

As I understand it, the CASE statement, @AdminApproved should never be anything other than 0 or 1. It is impossible that it could be NULL. And still I get NULL !!!! Can someone help me for the same thing?

Answer 1

The work of your sample code

According to SQL Fiddle, this works. It is either set to 0 or 1. Therefore, if you use exactly the code that you show us, this variable cannot be null .

 declare @AdminApproved1 bit declare @AdminApproved2 bit select @AdminApproved1 = case when (null) is null then 0 else 1 end, @AdminApproved2 = case when (getdate()) is null then 0 else 1 end -- both have values select @AdminApproved1, @AdminApproved2; 

But the variable can still be null

So, if your sample code is not complete, I can show you another example where both top variables will be null even after select executed. And I suspect your problem is related.

 declare @AdminApproved1 bit declare @AdminApproved2 bit select @AdminApproved1 = CASE WHEN (null) IS NULL THEN 0 ELSE 1 END, @AdminApproved2 = CASE WHEN (getdate()) IS NULL THEN 0 ELSE 1 END from SomeTable where 0 = 1 -- this query yields no results -- both are NULL when select @AdminApproved1, @AdminApproved2; 

Answer 2

You cannot get null from this request, so there should be something else in your code that is wrong. It is likely that there is something that allows this request to be performed at all.

Test to verify the result:

 declare @AdminApproved int declare @AdminApprovalDate datetime -- testing with null set @AdminApprovalDate = null SELECT @AdminApproved = CASE WHEN @AdminApprovalDate IS NULL THEN 0 ELSE 1 END select @AdminApproved -- testing with a date set @AdminApprovalDate = getdate() SELECT @AdminApproved = CASE WHEN @AdminApprovalDate IS NULL THEN 0 ELSE 1 END select @AdminApproved 

Output:

 0 1 

Answer 3

Assigning a value using SELECT will retain the original value before the assignment if there are no rows within the selection line. (Actually it’s not that the value will be NULL , it will be a stored value.

The only possible case is SET, that an assignment that does not return a string sets to NULL .

So, in your special case, the value of the previous value was NULL (because you did not assign a value), and your choice did not return.