CodeSOD: The Wisdom of the Ancients
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 fnValueChangedPurpose: Returns a value based on the parameters passed in which will tell the user whether or not 2 values have changedUsage 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 intASBegin 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] Release!is a light card game about software and the people who make it. Play with 2-5 people, or up to 10 with two copies - only $9.95 shipped!