SET statement considers 0 as null

I don’t how to explain my problem but I will try my best to be clear.

I am trying to create a stored procedure that will be used to handle holidays requests. I have two tables:

Table A contains the name of the holiday and the max number of days that can be requested in a year. It goes like this

TABLE A
------------------------
| HOLIDAY NAME| QUOTA  |
------------------------
| Yearly      | 40     |
------------------------

Table B tracks all the holidays requests and automatically updates the remaining number of days for a specific holiday. After multiple requests, the column ‘Balance’ determines how many days are still available. In the case below, the balance is 0 after the last request.

TABLE B
----------------------------------------------------
| HOLIDAY NAME | FROM       | TO         | BALANCE |
----------------------------------------------------
| Yearly       | 2019-10-12 | 2019-10-22 | 0       |
----------------------------------------------------

What I want is that, whenever the balance is 0, a request for a new holiday should not get through. To achieve this, I have applied the following logic (an extract of my procedure):

DECLARE @quota_requested int, @quota int, @balance int, @balance_new int, @period_from date, @period_to date
SET     @period_from = '2019-12-27'
SET     @period_to = '2019-12-31'
SET @quota_requested = (datediff(dd, @period_from, @period_to) + 1) - (datediff(wk, @period_from, @period_to)*2) - (CASE WHEN datename(dw,@period_from) = 'Sunday' THEN 1 ELSE 0 END) - (CASE WHEN datename(dw, @period_to) = 'Sunday' THEN 1 ELSE 0 END)
SET @balance = (SELECT balance FROM dbo.TABLE_B WHERE holiday = 'Yearly')
SET @quota = (SELECT quota FROM dbo.TABLE_A WHERE holiday = 'Yearly')

IF @balance is null
    SET @balance_new = @quota 
ELSE 
    SET @balance_new = @balance

What I do in this code is:

I compute the number of days that a person requests (@quota_requested)

I compare it with the quota that is allowed for that holiday (@quota)

* If @quota_requested > @quota: Don't proceed
* If @quota_requested <= @quota: Proceed to next check

I compare the number of days requested with the balance for that holiday (@balance)

* If @quota_requested > @balance: Don't proceed
* If @quota_requested <= @balance: Insert the request into the table

This logic works very well whenever the balance > 0 but when the balance gets to 0, the following part of the code:

SET @balance = (SELECT balance FROM dbo.TABLE_B WHERE holiday = 'Yearly')

passes NULL to the variable @balance even though the balance = 0. As a result, I am never able to stop the insert when the balance gets to zero and the code starts the balance to the value of the quota due to this part of the code:

IF @balance = null or @balance = '' or @balance is null
    SET @balance_new = @quota 
ELSE 
    SET @balance_new = @balance

I would really like to know what is going on here and why 0 is being treated as NULL. May this be due to the SET function? I changed to SELECT but this didn’t change a thing.

Thank you

Answer

Crystal ball answer

The problem is you think that 0 and '' are different values; with numerical data types you would be wrong. Try this:

SELECT CASE WHEN 0 WHEN '' THEN 0 END;

You get the result 0 right? That’s because '' was converted the to an int and as an int the value is 0.

That’s why your IF results in true. I’m not honestly sure why you’re comparing an int to a varchar though.

Advertisement

Answer