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,
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`
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`.
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
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