I have used this syntax before:
ISNULL(NULLIF(checkValue, Null), yesValue)
- The checkValue will be checked if it is Null
- If checkValue is Null then it is replaced with the yesValue
- If checkValue is not Null then checkValue will be returned
I have taken over a project which has the following syntax and I’m not sure what it’s doing…
Any help appreciated
IIF(ISNULL(UG.groupID, '') = '', '', UG.groupID) AS groupID
>Solution :
From the official documentation at Logical Functions – IIF (Transact-SQL) (it says 2 minutes to read):
IIF is a shorthand way for writing a CASE expression.
…
The fact that IIF is translated into CASE also has an impact on other aspects of the behavior of this function.
…A. Simple IIF example
DECLARE @a INT = 45, @b INT = 40; SELECT [Result] = IIF( @a > @b, 'TRUE', 'FALSE' );
Equipped with this knowledge we can make an experiment:
SELECT groupId, IIF(ISNULL(groupId, '') = '', '', groupId)
FROM (VALUES (NULL), (''), ('abc')) AS T(groupId)
This gives:
(No column name) (No column name)
NULL
abc abc
Now that we have our test we can try to simplify the sql. The way it works now is:
- if group id is null then make it ” and then compare to ” to get true/false
- if true (was null) then use ”,
Would this work instead?
SELECT groupId, IIF(groupId is null, '', groupId)
FROM (VALUES (NULL), (''), ('abc')) AS T(groupId)
Yes, it works.
However we can go deeper.
SELECT groupId, COALESCE(groupId, '')
FROM (VALUES (NULL), (''), ('abc')) AS T(groupId)