CASE statement with IS NULL and NOT NULL

  • Is there any better way to write the lines below in SQL Server 2005?

    CASE
    WHEN (ID IS NULL)
       THEN 'YES'
    WHEN (ID IS NOT NULL)
       THEN 'NO'
    END AS ID_Value,
    

    Define "better".

    I'm not familiar with MSSQL but if it has something like IF-function in MySQL, you can write such code: `IF(ID IS NULL, 'YES', 'NO') AS ID_Value`

    SQL Server 2012 has `IIF` but the question is tagged 2005.

    You should be able to accomplish this with the statement below. `isnull(nullif(isnull(ID,'Y'),ID),'N')`

    @Jason Assuming `ID` is a string, of course. Otherwise this expression should be elongated a little more by a call to `CAST` or `CONVERT`.

    Tried using `COALESCE`? `CASE COALESCE(ID,0) WHEN 0 THEN 'YES' ELSE 'NO' END`

    @Bappy1988 Why would it be better than Jay's answer?

    This does not work if the column in the test is of IMAGE datatype. SQL Server throws the following error: "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

    You are looking for something like the ?: operator in C# and there is no direct equivalent in tsql https://msdn.microsoft.com/en-us/library/ty67wk28.aspx I suspect you could write a function to do it https://msdn.microsoft.com/en-us/library/ms186755.aspx

  • Did you try:

    CASE WHEN (ID IS NULL) THEN 'YES' ELSE 'NO' END AS ID_Value,
    

    I only have access to 2008 right now, but I'd hope that this syntax would still work in 2005 (seems like something that would be part of the original definition of CASE).

    Yes - I did try `CASE WHEN (ID IS NULL) THEN 'YES' ELSE 'NO' END AS ID_Value` But I am looking for some other better approach something like `IF(ID IS NULL, 'YES', 'NO') AS ID_Value` in the Ms Sql, so that everything can be in a single line. Any suggestions please

    This answer is in a single line. If you want you can put the whole statement in a single line.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM