locked
ISNULL("Check","Replacement") function with 3 values or "CASE WHEN THEN ELSE" in short RRS feed

  • Question

  • <Edit> I am really sorry if this is the wrong forum, I checked all available forums and none seemed to fit by my understanding </Edit>

    Hello everyone,

    I tried to find it but wasn't able to. I used "case when X is null then Y else X" for years and just found out about ISNULL(X,Y) recently which is by far a better solution and I am using it since then.

    I wanted to overhaul bigger Queries with these changes but stumbled upon the issue of "IF X is Condition THEN 'Y' ELSE 'Z'"

    Condition can be "is null" as asked for in the title, best case would be if it also allows other checks, so...a short CASE WHEN THEN ELSE function


    • Edited by OoDex Monday, March 9, 2020 9:17 AM
    Monday, March 9, 2020 9:17 AM

All replies

  • ISNULL works only for NULL states, you have to handle all other condition with CASE WHEN.

    BTW, you can also use COALESCE (Transact-SQL), it accepts n values instead of only two AS ISNULL and is ANSI compliant.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, March 9, 2020 10:53 AM

  • Condition can be "is null" as asked for in the title, best case would be if it also allows other checks, so...a short CASE WHEN THEN ELSE function

    You can check Coalesce as well as IIF function fits for your requirement. 

    <Edit> I am really sorry if this is the wrong forum, I checked all available forums and none seemed to fit by my understanding </Edit>

    --> Next time, you may post the question in SQL Server > Transact-SQL forum for sql query related asks.


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, March 9, 2020 12:20 PM
  • In addition to other posts: coalesce is just syntactic sugat for the CASE expression you have been using all through the years.
    isnull on the other hand is really a function. There are some subtle differences between the two. isnull has fewer surprises, so stick with it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, March 9, 2020 10:28 PM