Wrapping ISNULL around NULLIF

Question

Suppose that I have:

 case  
    when @ID ='2386002' then ISNULL(nullif(i.call,''),i.standingOrderNumber)  
    when nullif(rtrim(i.call),'') is null then 
            nullif(rtrim(i.standingOrderNumber), '')
    else case when nullif(rtrim(i.standingOrderNumber),'') is null then rtrim(i.call) 
         else  
            rtrim(i.call)

This is just a part of the procedure which does synchronization between two apps, the problem is that standingOrderNumber is not synchronized and I assume it has to to do with this code portion.

Scenario: After entering call and standingOrderNumber like this:

call: '' (leave it empty)

standingOrderNumber: 777777

Data is stored in a table and procedure takes the data from that table and displays it on the app form, problem is, everything is displayed correctly, except for this standingOrderNumber.

Can you tell if something went wrong in the logic I submitted above ?

When you submit the form is it truly passing an empty or is it converting '' to NULL? — Nate Reynolds, Jun 13 '18 at 13:06
Oops, typo... I omitted 'string' after empty. Perhaps incorrectly, I assumed this procedure was receiving data from a form, but now after rereading it appears it's the other way. To be more specific, what is the source of input for this procedure? It would be helpful to also see the full code (with the variable declarations) — Nate Reynolds, Jun 14 '18 at 11:46

score 0 · Answer 1 · answered Jun 13 '18 at 12:53

I think problem with the below line,

when @ID ='2386002' then ISNULL(nullif(i.call,''),i.standingOrderNumber)

here you are trying to check i.call for null value and replacing it with empty string and that makes ISNULL function useless.

Solution is not to use nullif in above line

Wrapping ISNULL around NULLIF

1 Answers1