Newb - SQL Server IIF() Function

  • This is great.. I am making progress and learning. LOL.

    Can I do this, within the same statement?

    IIF(var1Title != '', CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name), '') AS Item1, IIF(var1Title != '', CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name), '') AS Item1,

    IIF(var2Title != '', CONCAT(var2Title, ' - '+var2Topic, ' - '+var2Name), '') AS Item2, IIF(var2Title != '', CONCAT(var2Title, ' - '+var2Topic, ' - '+var2Name), '') AS Item2

    From Table

  • FGJ wrote:

    This is great.. I am making progress and learning. LOL.

    Can I do this, within the same statement?

    IIF(var1Title != '', CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name), '') AS Item1, IIF(var1Title != '', CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name), '') AS Item1,

    IIF(var2Title != '', CONCAT(var2Title, ' - '+var2Topic, ' - '+var2Name), '') AS Item2, IIF(var2Title != '', CONCAT(var2Title, ' - '+var2Topic, ' - '+var2Name), '') AS Item2

    From Table

     

    Awesome... now you know the reason for all the questions about what you expected if "displayed".  Do you have any questions on how CONCAT works its magic there?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • FGJ wrote:

    Can I do this, within the same statement?

    You've doubled up the output columns, I presume that is just a typo?

    but ... that apart ... test it 🙂

    SELECT	MyID,
    CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name) AS CONCATTest,
    IIF(var1Title != ''
    , CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name)
    , '') AS Item1
    FROM
    (
    SELECT 1 AS MyID, 'MyTitle' AS var1Title, 'MyTopic' AS var1Topic, 'MyName' AS var1Name
    UNION ALL SELECT 2, NULL, 'MyTopic', 'MyName'
    UNION ALL SELECT 3, 'MyTitle', NULL, 'MyName'
    UNION ALL SELECT 4, 'MyTitle', 'MyTopic', NULL
    UNION ALL SELECT 5, NULL, NULL, 'MyName'
    UNION ALL SELECT 6, 'MyTitle', NULL, NULL
    UNION ALL SELECT 7, NULL, 'MyTopic', NULL
    UNION ALL SELECT 8, NULL, NULL, NULL
    ) AS T
    ORDER BY MyID

    But I would still recommend that you use CASE instead

    SELECT	MyID,
    CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name) AS CONCATTest,
    IIF(var1Title != ''
    , CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name)
    , '') AS Item1CONCAT,
    CASE WHEN var1Title != ''
    THEN CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name)
    ELSE ''
    END AS Item1CASE

    FROM
    (
    SELECT 1 AS MyID, 'MyTitle' AS var1Title, 'MyTopic' AS var1Topic, 'MyName' AS var1Name
    UNION ALL SELECT 2, NULL, 'MyTopic', 'MyName'
    UNION ALL SELECT 3, 'MyTitle', NULL, 'MyName'
    UNION ALL SELECT 4, 'MyTitle', 'MyTopic', NULL
    UNION ALL SELECT 5, NULL, NULL, 'MyName'
    UNION ALL SELECT 6, 'MyTitle', NULL, NULL
    UNION ALL SELECT 7, NULL, 'MyTopic', NULL
    UNION ALL SELECT 8, NULL, NULL, NULL
    ) AS T
    ORDER BY MyID

    Using IIF the only thing separating the Logic Test, and the First and Second parameters, is a COMMA. When the parameters start to get complicated (as is the case here, with the CONCAT also having several commas), typing mistakes take longer to spot and longer to solve when all you get is a "Syntax error at ',' " message.

    Most SQL Editors will colour code keywords such as CASE WHEN THEN ELSE END which also makes it easier to spot when something is missed off. Plus, as said earlier, when you have more than two choices CASE is far easier to read than lots of nested IIFs

    You might say "I'll use IIF for simple true/false and CASE for complicated ones". My answer would be : What happens when the IIF needs another condition adding? Are you going to then convert IIF to CASE? We very specifically avoid ALL code changes like that here, so we code using layout and methods that make it easy to modify the code in future, in a way that discourages bugs.

    TEMP_SyntaxHighight

  • Here is an alternative that doesn't require the use of CASE or IIF:

    Declare @testTable Table (var1Title varchar(30), var1Topic varchar(30), var1Name varchar(30));
    Insert Into @testTable (var1Title, var1Topic, var1Name)
    Values ('Title 1', 'Topic 1', 'Name 1')
    , ('Title 2', 'Topic 2', Null)
    , ('Title 3', Null, 'Name 3')
    , (Null, 'Topic 4', 'Name 4')
    , ('Title 5', Null, Null)
    , (Null, 'Topic 6', Null)
    , (Null, Null, 'Name 7')
    , (Null, Null, Null);

    Select *
    , TitleColumn = concat(tt.var1Title, ' - ', tt.var1Topic, ' - ', tt.var1Name)
    , TitleColumn2 = concat(tt.var1Title, ' - ' + tt.var1Topic, ' - ' + tt.var1Name)
    , TitleColumn3 = concat(tt.var1Title + ' - ', tt.var1Topic + ' - ', '' + tt.var1Name)
    , TitleColumn4 = stuff(concat(' - ' + tt.var1Title, ' - ' + tt.var1Topic, ' - ' + tt.var1Name), 1, 3, '')
    From @testTable tt;

    TitleColumn4 will return a NULL if all values are NULL - else it will return just the items that are not NULL separated by a ' - '.  If you don't want a NULL value returned in that situation then you can add COALESCE:

          , TitleColumn4 = coalesce(stuff(concat(' - ' + tt.var1Title, ' - ' + tt.var1Topic, ' - ' + tt.var1Name), 1, 3, ''), '')

     

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks all, got it to work!! Much appreciated!!

  • Kristen-173977 wrote:

    I dislike IIF ... its "familiar" for people that use similar functions in Excel, but I think CASE is better in SQL

    IIF only has two outcomes, true/false, and to get multiple tests the blessed things are nested and becomes write-only code

    100% agreed.  I despise IIF even being put into T-SQL.  If MS keeps this up, T-SQL will be the same horrific mish-mash of multiple language styles that [Oracle's] PL/SQL became.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Under the hood SQL Server converts an IIF to a CASE

    sql

  • Jonathan AC Roberts wrote:

    Under the hood SQL Server converts an IIF to a CASE

    sql

    There are uses for IIF - just the same as CHOOSE and other shortcut features that have been added to SQL Server.  I disagree that adding these are problematic - just because they can be done using a CASE expression.

    Usage of these can be abused...and one of the most egregious is stacking/embedding multiple IIF statements.  But then again - CASE can be abused too - the most prominent being: CASE WHEN col1 IS NULL THEN 'default value' ELSE col1 END, which can and should be rewritten to use either ISNULL or COALESCE.

    The same argument that @Kristen makes for changing IIF to a CASE expression applies to ISNULL - are you going to change ISNULL to COALESCE when the requirements change and you need to expand from a single expression to multiple expressions?  Or - do you allow stacked ISNULL statements?  Or - do you not allow the use of either ISNULL or COALESCE because this can all be done using a CASE expression?

    What about NULLIF - avoid using that because a CASE expression can do the same thing?  For example: CASE WHEN col1 = 'some value' THEN NULL ELSE col1 END vs NULLIF(col1, 'some value').  Is it bad to use stacked NULLIF statements - and therefore better to use CASE everywhere instead - because at some future point in time you might need to return NULL based on multiple values?

    If the standard is that a 'future' change might be needed - that would require changing from the existing function to a different function or CASE expression - then I would assume that any function that can be done using a CASE expression should not be allowed - at all.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • CASE, ISNULL and COALESCE are all an integral part of SQL Server and follow its general syntax.  It's because IIF is so unlike other other functions and expressions in SQL that it shouldn't be there.  That is, nowhere else in SQL does it enclose conditions and the results of it in parentheses separated by commas.  It's like putting Russian in the middle of English, it's just jarring.

    As to COALESCE and ISNULL, presumably ISNULL would never have been created had COALESCE been available in T-SQL earlier.  In theory ISNULL should be removed, but legacy issues prevent that.  ISNULL has some bad quirks that can cause problems, although I admit I still use it at times, simply out of habit, although I never embed it (also, btw, NEVER use ISNULL (or COALESCE) in a WHERE or JOIN clause).

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    CASE, ISNULL and COALESCE are all an integral part of SQL Server and follow its general syntax.  It's because IIF is so unlike other other functions and expressions in SQL that it shouldn't be there.  That is, nowhere else in SQL does it enclose conditions and the results of it in parentheses separated by commas.  It's like putting Russian in the middle of English, it's just jarring.

    So you argument is because the first parameter of the IIF function is a conditional expression that determines which value is to be returned?  Not sure how that is any different than ISNULL\COALESCE - which *hides* the evaluation and overloads each parameter to be both input and output parameters - where IIF specifically identifies the input parameter and separate possible output parameters.

    I get that it seems to be jarring...but to say it shouldn't be there just because it isn't an expression itself (it is a scalar function after all) doesn't seem to be a valid reason.

    To further state that it shouldn't be used because of that...well I have to disagree.  It is a useful function - as in any function - when it is used appropriately.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    The same argument that @Kristen makes for changing IIF to a CASE expression applies to ISNULL - are you going to change ISNULL to COALESCE when the requirements change and you need to expand from a single expression to multiple expressions?  Or - do you allow stacked ISNULL statements?  Or - do you not allow the use of either ISNULL or COALESCE because this can all be done using a CASE expression? 

    We never use ISNULL for that very reason. Changing code from ISNULL to COALESCE (in order to allow 3+ parameters) would be a potential source of bugs (ISNULL and COALESCE don't perform the same with mixed data types). Anyway, I'm dyslexic and ISNULL doesn't make sense to me for something that acts on something that is NOT null ...

    We also never use SET to set an @VARIABLES because it can only set one per statement, and thus have to use SELECT to get @@ROWCOUNT and @@ERROR, so for consistency we always use SELECT to "set" @VARIABLES

    What about NULLIF - avoid using that because a CASE expression can do the same thing?

    I don't have a problem with that, because we never use NULLIF in a situation where we might want to "expand" it to multiple conditions. In fact I'd never considered it as a possibility until now ...

    I suppose this sort of stacking is OK though 🙂

    SELECT COALESCE(NULLIF(SomeColumn, ''), NULLIF(OtherColumn, ''), ...)

    I'd love to have some sort of STRICT option that disallowed some legacy things ... not allowing AS to be optional, and so on.

  • Jeffrey Williams wrote:

    ScottPletcher wrote:

    CASE, ISNULL and COALESCE are all an integral part of SQL Server and follow its general syntax.  It's because IIF is so unlike other other functions and expressions in SQL that it shouldn't be there.  That is, nowhere else in SQL does it enclose conditions and the results of it in parentheses separated by commas.  It's like putting Russian in the middle of English, it's just jarring.

    So you argument is because the first parameter of the IIF function is a conditional expression that determines which value is to be returned?  Not sure how that is any different than ISNULL\COALESCE - which *hides* the evaluation and overloads each parameter to be both input and output parameters - where IIF specifically identifies the input parameter and separate possible output parameters.

    I get that it seems to be jarring...but to say it shouldn't be there just because it isn't an expression itself (it is a scalar function after all) doesn't seem to be a valid reason.

    To further state that it shouldn't be used because of that...well I have to disagree.  It is a useful function - as in any function - when it is used appropriately.

    C'mon, it's hard to say that "ISNULL" hides the fact that it's checking for NULL.  That and COALESCE always and only check for a single value.  There's no inline freeform expression followed by just a comma followed by other values.

    Similarly, CASE uses a clear, readable format that includes keywords between the expression and the result (WHEN ... THEN).

    IIF is not actually used in SQL, instead it gets converted to CASE.  That alone is absolute proof that IIF is not integral to T-SQL and is thus a kludgy paste-on.  No, my objection is that the expression is followed by a comma and then the other value.  Nowhere else (I can think of) in T-SQL is that ever done.  For example, a regular IF is not like this:

    IF ...expression... , <if_true_action(s)>, ELSE <if_false_action(s)>

    If .Net developers can't figure out how to use a CASE statement, then train them better or get developers who can.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • And yet - ISNULL, COALESCE, NULLIF are all converted to a CASE expression (not statement) under the covers so they all should not be included by your argument.

    Your argument against IIF is the requirement that the first parameter is a conditional expression - or rather a Boolean expression and that it doesn't follow the 'normal' expression syntax.  Problem is IIF is not an expression - it is a function, the same as ISNULL, COALESCE, NULLIF.

    If these functions do the same thing - how is one any different to the other?

     

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ScottPletcher wrote:

    ISNULL has some bad quirks that can cause problems, although I admit I still use it at times, simply out of habit, although I never embed it (also, btw, NEVER use ISNULL (or COALESCE) in a WHERE or JOIN clause)

    COALESCE has a nasty quirk, as well.  You know what it is... it uses whatever the highest precedence datatype there is in the operands.  ISNULL doesn't have that problem.

    As with all else in SQL Server, "It Depends" on what you need to do and how you need to do it.  And, it's fine to use ISNULL (or COALESCE) in a WHERE or JOIN just so long as it's not on a column. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I have to tell a story. The choice of the keyword COALESCE () is due to Phil Shaw from IBM, one of the smartest guys that ever served on the X3H2 committee. When we were debating about what keyword to use, Phil pulled out a thesaurus (yes, he kept a thesaurus, a dictionary, Roberts Rules of Order, and a whole bunch of ANSI ISO standards on his person at all times) we were looking for a word that we thought nobody would ever really use an actual schema for a column or anything else. We came up with COALESCE.

    Picking the highest precedent operand in the parameter list was very, very deliberate. It's the only thing that makes any sense. If you play with it for a while, it will come to the same conclusion. The ISNULL() dialect function picks up the type from the first parameter. This means that the type of the expression depends on the ordering of the parameters. And what's one thing we really hate in RDBMS? Ordering! After we standardize things, of course, the other problem was that this dialect became just that – a local dialect with nonstandard behavior.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 15 posts - 16 through 30 (of 62 total)

You must be logged in to reply to this topic. Login to reply