• Paula Bean (unregistered)

    I'm frist?! Brillant!

  • Nicholas "LB" Braden (github)

    Hungarian notation sends shivers down my spine...spooky...

  • Rpresser (unregistered)

    Remy, you poor idiot... TSQL has no If.

  • Sham (unregistered) in reply to Rpresser

    Nope, none at all

    https://msdn.microsoft.com/en-us/library/ms182717.aspx

  • SQL NULL has its quirks (unregistered)

    When the function is used in SQL it might make more sense

    A comparision with NULL is always false (unless of course I compare it with IS NULL or IS NOT NULL), so comparing values becomes cumbersome when both values could be NULL

    ... where (colum1 = column2 and column1 is not null and column2 is not null or (column1 is null and column2 is null))

    (or the very same done with IsNull).

    Now we'll have to remember as well that in the SQL Standard BOOLEAN still is optional and not implemented by many RDBMS (e.g. Oracle) which might explain that the function returns either 1 or 0 instead of true or false.

    if the function would have been used in a SQL Query in the form

    where fnValueChanged(col1, col2, 'blah') = 1

    this might explain the original usage behind the function - a not SQL happy developer who simply didn't like this and implemented his own functions to be used for comparision (there was an article about that in the past: http://thedailywtf.com/articles/ch-ch-ch-changes)

    I still don't get the sense of the third (unused) parameter though....

    cheers

  • JonasKlose (unregistered)

    When IsNull(@pNewValue, '') <> IsNull(@pOldValue, '') and @pNewValue Is Not Null Then 1 Else 0

    That's effectively if pNewValue is set and pOldValue is Null, then this thing returns 1, else 0.

    Which means that the new password can only be applied if the old password is NULL. Which, of course, shouldn't be....

    When dbo.fnValueHasChanged(@LocPasswordOld, @LocPassword, NULL) = 1 then @LocPassword Else @LocPasswordOld

  • murgo (unregistered)

    JonasKlose, IsNull(val, defaultval) returns val if it is not null. So the "when" works fine (IF is not supported on many situations like inside the SET as used here IIRC).

    The part where it's used makes no sense though, it could just be replaced with CarPassword = @LocPassword

  • djdahmer (unregistered)

    There is also a bug when pNewValue = '' and pOldValue is NULL: ISNULL() would return '' in both cases & the function fnValueHasChanged would return 0, so the calling SQL would set CarPassword to LocPasswordOld (NULL) instead of LocPassword ('')

  • Ali Razeghi (google) in reply to Sham

    In all fairness to Represser and his 'There are no tanks in Baghdad' statement, it was only introduced in 2012. Sadly he chose to pick an elitist path and make fun of someone's lack of info, when it was he who was wrong the whole time. Finally, in fairness to the code, IF statement might not have existed then but wow what a convoluted way of doing it.

  • jay (unregistered)

    How, pray tell, would you write this better?

    TSQL does not provide for setting a boolean variable to the result of an expression. So you can't just say "hasChanged = valueA != valueB". You have to use a CASE statement to set the value. Yes, you could use an IF and write

    if ... condition ... BEGIN
 set @flag = 1
ELSE
  set @flag = 0
END;

    but that is not clearly better than a CASE statement. For one thing it turns one SET into two, which forces the reader to check if the flag being set is the same on both branches of the IF.

    If you tried to just say "oldValue != newValue", then if either one is null the return value is null, which sort of looks like false. So with "CASE WHEN oldValue!=newValue then 1 else 0 end", if one value was null and the other not null, would return 0, which is indistinguishable from "not changed".

    SQL is goofy about nulls, often making simple comparisons like this a beast. Other than the unused third parameter, I don't really see a blatant flaw here.

  • jay (unregistered) in reply to SQL NULL has its quirks

    Just to be pedantic, a comparison with null is not really "always false". SQL has trinary logic: The result of a comparison or logical operation can be true, false, or null. Any compare to null returns null, neither true nor false.

    If you then just test this value, a null looks like false. But if you do further operations, the null propagates. I think the key difference is that NOT FALSE is TRUE, while NOT NULL is NULL. So for example, CASE WHEN x=y THEN 1 ELSE 0 END returns 0 if x and/or y are null. So it looks like comparing to null returns false. But CASE WHEN NOT x=y THEN 1 ELSE 0 END also returns 0 if x and/or y are null. Doing a NOT on the compare doesn't change the result to true. It looks sort of like NOT FALSE = FALSE.

    And by the way, if both x and y are null, x=y returns null, not true. So CASE WHEN x=y then 1 ELSE 0 END returns 0 if both are null.

  • Norman Diamond (unregistered)

    The result of a comparison or logical operation can be true, false, or null.

    What? On this site, of all places? How was that file found?

  • Wyrm (unregistered)

    As if the code itself was not enough of a WTF, another thing is how this function "returns int", but the value that is returned is declared as "tinyint" (same as the comment at the top states) and the value is either 0 or 1, which actually makes it a bit. Have fun with types. :)

  • But I am a robot (unregistered)

    Not suggesting that it is better, but here is a more sqly way using a self join on a table variable:

    DECLARE @new NVARCHAR(20) = 'hello' DECLARE @old NVARCHAR(20) = 'different'

    DECLARE @t TABLE( newVal NVARCHAR(20), oldVal NVARCHAR(20))

    INSERT INTO @t ( newVal, oldVal ) VALUES ( @new, @old)

    SELECT COUNT(1) FROM @t table1 INNER JOIN @t table2 ON table1.newVal = table2.oldVal

  • Helten (unregistered) in reply to JonasKlose

    Correction: if pNewValue is set and is different from pOldValue, then this thing returns 1, else 0.

    Which means that the new password will be applied whenever it is NOT NULL. Which sounds reasonable. The implementation, however, isn't the best.

  • jay (unregistered) in reply to Norman Diamond

    "What? On this site, of all places? How was that file found?"

    I'm sorry, I don't understand your comment. How was what file found? Who is talking about a file?

  • FuuzyFoo (unregistered) in reply to Ali Razeghi

    Hmmm. the linked web page from Microsoft says it was available in SQL 2008.

  • (nodebb)

    And the usage pattern given in the comment is wrong too. The function only accepts 3 parameters instead of N, with the third parameter not be used anywhere.

  • Norman Diamond (unregistered)

    The result of a comparison or logical operation can be true, false, or null.

    What? On this site, of all places? How was that file found?

    I'm sorry, I don't understand your comment. How was what file found? Who is talking about a file?

    One of the great classics of this site is that the result of a comparison or logical operation can be true, false, or filenotfound.

  • The "Mr" (unregistered)

    The correct way to do this is:

    CarPassword = ISNULL(@LocPassword, @LocPasswordOld)

  • Barf4Eva (unregistered) in reply to The "Mr"

    yep, agree. Simple and to the point. Can't believe this is the first mention of it...

  • isthisunique (unregistered)

    So basically use the one that is set, if both are set, use one of them by default. If neither are set, profit?

  • (nodebb) in reply to The "Mr"

    I admire how self-explanatory the name ISNULL is.

    (I mean, from a Sophisticated Quirky Language, what would you expect anyway.)

  • jay (unregistered) in reply to Norman Diamond

    Ah. I didn't catch the allusion. Yes, I remember that classic now.

  • Tureba (unregistered)

    Should have been as simple as: RETURN (@pNewValue IS DISTINCT FROM @pOldValue);

Leave a comment on “The Wisdom of the Ancients”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article