As Halloween descends upon us, mysterious emails start reaching our inbox. These plaintive missives are but the screams of the damned, encoded and sent over SMTP.

For example, someone known to us only as DBA Guy sent an email with this subject: “Silver bullet SQL scalar function built by the Ancient Ones”.

These ancient ones obviously did not come from the Euclidian plane we know so well, but obviously from a twisted, higher dimensional space where there exist no right angles.

The code itself, is simple:

/*--------------------------------------------------------------------------------------------------
Name                    fnValueChanged
Purpose:                Returns a value based on the parameters passed in which will tell the user whether or not 2 values have changed
Usage                   Select dbo.fnValueChanged(Parameter1,Parameter2,Parameter3,ParameterN...)
Returns         tinyint
--------------------------------------------------------------------------------------------------*/
ALTER          FUNCTION [dbo].[fnValueHasChanged]
(
        @pOldValue varchar(255),
        @pNewValue varchar(255),
        @pItemType varchar(40)                  -- to be used if we need to do date comparisions etc.
)
RETURNS int
AS
Begin
        Declare @vHasChanged tinyint
        set @vHasChanged = (
                Case
                        When IsNull(@pNewValue, '') <> IsNull(@pOldValue, '') and @pNewValue Is Not Null Then 1
                        Else 0
                End
        )
        RETURN (@vHasChanged)
End

Given an old value and a new value, determine if they’re different. It’s awkward and strange, with dead parameters coming to us from across the aeons, and an awkward Case statement when an If would probably be clearer and easier to understand. But how, praytell, is this code used?

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

The code which calls the function must be the same as the code within the function. Thus, we have a case within a case, a mystery within a mystery, and a function which has ben handed down to us from the ancient ones.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!