It might surprise people, but SQL Server CAST/TRY_CAST and CONVERT/TRY_CONVERT will change an empty string (‘’ or N’’) to a default value instead of NULL. This happens for numeric data types such as int, bigint, smallint, tinyint, numeric, float, bit, datetime, smalldatetime, datetime2, datetimeoffset. Also note that some data types will throw an exception on empty string such as decimal, numeric, and uniqueidentifier.

If you want to return NULL instead of 0, then use NULLIF first, e.g. CONVERT(int, NULLIF(@variable, ‘’))

DECLARE @a nvarchar = ''

-- ==== Example 1A: int (empty string) ====
-- Returns 0 instead of NULL
      SELECT data_type = 'int', title = 'CONVERT'           , value =     CONVERT(int, @a)
UNION SELECT data_type = 'int', title = 'TRY_CONVERT'       , value = TRY_CONVERT(int, @a)
UNION SELECT data_type = 'int', title = 'CAST'              , value =     CAST(@a AS int)
UNION SELECT data_type = 'int', title = 'TRY_CAST'          , value = TRY_CAST(@a AS int)
-- Returns
-- data_type | title       | value
-- --------- | ----------- | -----
-- int       | CAST        | 0
-- int       | CONVERT     | 0
-- int       | TRY_CAST    | 0
-- int       | TRY_CONVERT | 0

-- ==== Example 1B: int (NULLIF) ====
-- Returns NULL
      SELECT data_type = 'int', title = 'CONVERT+NULLIF'    , value =     CONVERT(int, NULLIF(@a, ''))
UNION SELECT data_type = 'int', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(int, NULLIF(@a, ''))
UNION SELECT data_type = 'int', title = 'CAST+NULLIF'       , value =     CAST(NULLIF(@a, '') AS int)
UNION SELECT data_type = 'int', title = 'TRY_CAST+NULLIF'   , value = TRY_CAST(NULLIF(@a, '') AS int)
-- Returns
-- data_type | title              | value
-- --------- | ------------------ | -----
-- int       | CAST+NULLIF        | NULL
-- int       | CONVERT+NULLIF     | NULL
-- int       | TRY_CAST+NULLIF    | NULL
-- int       | TRY_CONVERT+NULLIF | NULL


-- ==== Example 2A: datetime (empty string) ====
-- Returns 0 instead of NULL
      SELECT data_type = 'datetime', title = 'CONVERT'           , value =     CONVERT(datetime, @a)
UNION SELECT data_type = 'datetime', title = 'TRY_CONVERT'       , value = TRY_CONVERT(datetime, @a)
UNION SELECT data_type = 'datetime', title = 'CAST'              , value =     CAST(@a AS datetime)
UNION SELECT data_type = 'datetime', title = 'TRY_CAST'          , value = TRY_CAST(@a AS datetime)
-- Returns
-- data_type | title       | value
-- --------- | ----------- | -----------------------
-- datetime  | CAST        | 1900-01-01 00:00:00.000
-- datetime  | CONVERT     | 1900-01-01 00:00:00.000
-- datetime  | TRY_CAST    | 1900-01-01 00:00:00.000
-- datetime  | TRY_CONVERT | 1900-01-01 00:00:00.000

-- ==== Example 2B: datetime (NULLIF) ====
-- Returns NULL
      SELECT data_type = 'datetime', title = 'CONVERT+NULLIF'    , value =     CONVERT(datetime, NULLIF(@a, ''))
UNION SELECT data_type = 'datetime', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(datetime, NULLIF(@a, ''))
UNION SELECT data_type = 'datetime', title = 'CAST+NULLIF'       , value =     CAST(NULLIF(@a, '') AS datetime)
UNION SELECT data_type = 'datetime', title = 'TRY_CAST+NULLIF'   , value = TRY_CAST(NULLIF(@a, '') AS datetime)
-- Returns
-- data_type | title              | value
-- --------- | ------------------ | -----
-- datetime  | CAST+NULLIF        | NULL
-- datetime  | CONVERT+NULLIF     | NULL
-- datetime  | TRY_CAST+NULLIF    | NULL
-- datetime  | TRY_CONVERT+NULLIF | NULL


-- ==== Example 3A: float (empty string) ====
-- Returns 0 instead of NULL
      SELECT data_type = 'float', title = 'CONVERT'           , value =     CONVERT(float, @a)
UNION SELECT data_type = 'float', title = 'TRY_CONVERT'       , value = TRY_CONVERT(float, @a)
UNION SELECT data_type = 'float', title = 'CAST'              , value =     CAST(@a AS float)
UNION SELECT data_type = 'float', title = 'TRY_CAST'          , value = TRY_CAST(@a AS float)
-- Returns
-- data_type | title       | value
-- --------- | ----------- | -----
-- float     | CAST        | 0
-- float     | CONVERT     | 0
-- float     | TRY_CAST    | 0
-- float     | TRY_CONVERT | 0

-- ==== Example 3B: float (NULLIF) ====
-- Returns NULL
      SELECT data_type = 'float', title = 'CONVERT+NULLIF'    , value =     CONVERT(float, NULLIF(@a, ''))
UNION SELECT data_type = 'float', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(float, NULLIF(@a, ''))
UNION SELECT data_type = 'float', title = 'CAST+NULLIF'       , value =     CAST(NULLIF(@a, '') AS float)
UNION SELECT data_type = 'float', title = 'TRY_CAST+NULLIF'   , value = TRY_CAST(NULLIF(@a, '') AS float)
-- Returns
-- data_type | title              | value
-- --------- | ------------------ | -----
-- float     | CAST+NULLIF        | NULL
-- float     | CONVERT+NULLIF     | NULL
-- float     | TRY_CAST+NULLIF    | NULL
-- float     | TRY_CONVERT+NULLIF | NULL


-- ==== Example 4A: decimal (empty string) ====
-- Returns 0 instead of NULL
      SELECT data_type = 'decimal', title = 'CONVERT'           , value =     CONVERT(decimal, @a)
UNION SELECT data_type = 'decimal', title = 'TRY_CONVERT'       , value = TRY_CONVERT(decimal, @a)
UNION SELECT data_type = 'decimal', title = 'CAST'              , value =     CAST(@a AS decimal)
UNION SELECT data_type = 'decimal', title = 'TRY_CAST'          , value = TRY_CAST(@a AS decimal)
-- Throws Error 8114: Error converting data type nvarchar to numeric.

-- ==== Example 4B: decimal (NULLIF) ====
-- Returns NULL
      SELECT data_type = 'decimal', title = 'CONVERT+NULLIF'    , value =     CONVERT(decimal, NULLIF(@a, ''))
UNION SELECT data_type = 'decimal', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(decimal, NULLIF(@a, ''))
UNION SELECT data_type = 'decimal', title = 'CAST+NULLIF'       , value =     CAST(NULLIF(@a, '') AS decimal)
UNION SELECT data_type = 'decimal', title = 'TRY_CAST+NULLIF'   , value = TRY_CAST(NULLIF(@a, '') AS decimal)
-- Returns
-- data_type | title              | value
-- --------- | ------------------ | -----
-- decimal   | CAST+NULLIF        | NULL
-- decimal   | CONVERT+NULLIF     | NULL
-- decimal   | TRY_CAST+NULLIF    | NULL
-- decimal   | TRY_CONVERT+NULLIF | NULL


-- ==== Example 5A: numeric (empty string) ====
-- Returns 0 instead of NULL
      SELECT data_type = 'numeric', title = 'CONVERT'           , value =     CONVERT(numeric, @a)
UNION SELECT data_type = 'numeric', title = 'TRY_CONVERT'       , value = TRY_CONVERT(numeric, @a)
UNION SELECT data_type = 'numeric', title = 'CAST'              , value =     CAST(@a AS numeric)
UNION SELECT data_type = 'numeric', title = 'TRY_CAST'          , value = TRY_CAST(@a AS numeric)
-- Throws Error 8114: Error converting data type nvarchar to numeric.

-- ==== Example 5B: numeric (NULLIF) ====
-- Returns NULL
      SELECT data_type = 'numeric', title = 'CONVERT+NULLIF'    , value =     CONVERT(numeric, NULLIF(@a, ''))
UNION SELECT data_type = 'numeric', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(numeric, NULLIF(@a, ''))
UNION SELECT data_type = 'numeric', title = 'CAST+NULLIF'       , value =     CAST(NULLIF(@a, '') AS numeric)
UNION SELECT data_type = 'numeric', title = 'TRY_CAST+NULLIF'   , value = TRY_CAST(NULLIF(@a, '') AS numeric)
-- Returns
-- data_type | title              | value
-- --------- | ------------------ | -----
-- numeric   | CAST+NULLIF        | NULL
-- numeric   | CONVERT+NULLIF     | NULL
-- numeric   | TRY_CAST+NULLIF    | NULL
-- numeric   | TRY_CONVERT+NULLIF | NULL

-- 6: data_type = uniqueidentifier

-- ==== Example 6A: uniqueidentifier (empty string) ====
-- Returns 0 instead of NULL
      SELECT data_type = 'uniqueidentifier', title = 'CONVERT'           , value =     CONVERT(uniqueidentifier, @a)
UNION SELECT data_type = 'uniqueidentifier', title = 'TRY_CONVERT'       , value = TRY_CONVERT(uniqueidentifier, @a)
UNION SELECT data_type = 'uniqueidentifier', title = 'CAST'              , value =     CAST(@a AS uniqueidentifier)
UNION SELECT data_type = 'uniqueidentifier', title = 'TRY_CAST'          , value = TRY_CAST(@a AS uniqueidentifier)
-- Throws Error 8169: Conversion failed when converting from a character string to uniqueidentifier.

-- ==== Example 6B: uniqueidentifier (NULLIF) ====
-- Returns NULL
      SELECT data_type = 'uniqueidentifier', title = 'CONVERT+NULLIF'    , value =     CONVERT(uniqueidentifier, NULLIF(@a, ''))
UNION SELECT data_type = 'uniqueidentifier', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(uniqueidentifier, NULLIF(@a, ''))
UNION SELECT data_type = 'uniqueidentifier', title = 'CAST+NULLIF'       , value =     CAST(NULLIF(@a, '') AS uniqueidentifier)
UNION SELECT data_type = 'uniqueidentifier', title = 'TRY_CAST+NULLIF'   , value = TRY_CAST(NULLIF(@a, '') AS uniqueidentifier)
-- Returns
-- data_type        | title              | value
-- ---------------- | ------------------ | -----
-- uniqueidentifier | CAST+NULLIF        | NULL
-- uniqueidentifier | CONVERT+NULLIF     | NULL
-- uniqueidentifier | TRY_CAST+NULLIF    | NULL
-- uniqueidentifier | TRY_CONVERT+NULLIF | NULL

-- ==== JSON ====
-- NOTE: Empty string will default to integer 0 instead of NULL
DECLARE @json nvarchar(max) = N'[
{"a":0},
{"a":1, "b":2 },
{"a":3, "b":""}
]'

SELECT  a
    ,   b
FROM    OPENJSON(@json)
        WITH (
            a           int
        ,   b           int
        )
-- Returns:
-- a | b
-- 0 | NULL
-- 1 | 2
-- 3 | 0

-- NOTE: Must manually convert string to integer to avoid empty string defaulting to 0
SELECT  a   = TRY_CONVERT(int, NULLIF(a, ''))
    ,   b   = TRY_CONVERT(int, NULLIF(b, ''))
FROM    OPENJSON(@json)
        WITH (
            a           varchar(30)
        ,   b           varchar(30)
        )
-- Returns:
-- a | b
-- 0 | NULL
-- 1 | 2
-- 3 | NULL


-- ==== XML ====
-- NOTE: Empty string will default to integer 0 instead of NULL
DECLARE @xml xml = N'



'

SELECT  a   = t.x.value('@a', 'int')
    ,   b   = t.x.value('@b', 'int')
FROM    @xml.nodes('/xml/row') AS t(x)
-- Returns:
-- a | b
-- 0 | NULL
-- 1 | 2
-- 3 | 0

-- NOTE: Must manually convert string to integer to avoid empty string defaulting to 0
SELECT  a   = TRY_CONVERT(int, NULLIF(t.x.value('@a', 'varchar(30)'), ''))
    ,   b   = TRY_CONVERT(int, NULLIF(t.x.value('@b', 'varchar(30)'), ''))
FROM    @xml.nodes('/xml/row') AS t(x)
-- Returns:
-- a | b
-- 0 | NULL
-- 1 | 2
-- 3 | NULL

I have migrated my blog to dasBlog Core. Originally I used SubText and then later MiniBlog.Core and experimented with Jekyll but the exporting format wasn't what I wanted. So for the most part haven't blogged in about ten years and left it dormant. After all that time, I decided to resurrect the blog with a .NET Core based engine, although I will probably keep Jekyll with GitHub Pages as a backup mechanism.

Have you ever worked on a project that involved a requirement with an IF statement with several branches and multiple conditionals in each branch? Then the programmer takes the simplest approach and programs it exactly as it was written. Later, there are issues and it becomes very difficult to debug this logic.

I was working on a project recently that involved a complex calculation for the timeliness of a process order. The logic included checks to see if the process order was running (had any jobs started?), to see if any jobs started late or missing required finish times, and more comparisons. I was not responsible for the original implementation, but I was asked to investigate and fix it after the customer had issues with the calculations.

The specification included requirements such as (paraphrased):

• The status will be “DELAY” if any of the following conditions are true:
• the job has not started and the scheduled start time has passed but the latest start time has not passed yet.
• the job is running and it started after the scheduled start time but before the latest start time.
• the job is completed and it finished after the scheduled finish time but before the required finish time.
• The status will be “LATE” if any of the following conditions are true:
• the job has not started and both the scheduled and latest start times have passed.
• the job started after the latest start time.
• the job is completed and it finished after the required finish time.

The more complicated the logic and conditions, the more important it is to expose these details both for debugging purposes and for the user or administrator.

There were several issues I uncovered with the above specification as well as enhancements that I added.

One of the primary issues was the general status of either “DELAY” or “LATE” without indicating which condition was responsible for the status. For example, the customer noticed a process order was marked as “LATE” even though the required finish time had not elapsed. It turned out the process started late, but it was not easy to figure that out with the information provided on the screen at the time.

Consequently, I enhanced the status codes to return more information. There are two basic options: change the status codes, or add a sub-status code with the detailed information.

The typical implementation of conditions and logic look something like this (shown in a SQL-style language but it really does not matter which language is used):

My implementation was actually in SQL since the goal was to reduce the data returned to the client application. You could implement it in the client application, but that results significantly more data transmitted and slows the client application and decreases the available bandwidth for all users.

I was working on a spreadsheet that submitted XML data to a web service and the XML that was being generated by the XmlDocument object was not very tidy. Normally with C# I use Tidy.NET to clean up the HTML or XML, but since this was being used in Excel I didn't want to have any external dependencies. I found the following code online and it seems to work pretty well:

Option Explicit

' http://www.vb-helper.com/howto_formatted_xml_document.html
' Add formatting to the document.
Public Sub FormatXmlDocument(ByVal xml_doc As DOMDocument)
    FormatXmlNode xml_doc.documentElement, 0
End Sub

' Add formatting to this element. Indent it and add a
' carriage return before its children. Then recursively
' format the children with increased indentation.
Private Sub FormatXmlNode(ByVal node As IXMLDOMNode, _
                          ByVal indent As Integer)
Dim child As IXMLDOMNode
Dim text_only As Boolean

    ' Do nothing if this is a text node.
    If TypeOf node Is IXMLDOMText Then Exit Sub

    ' See if this node contains only text.
    text_only = True
    If node.hasChildNodes Then
        For Each child In node.childNodes
            If Not (TypeOf child Is IXMLDOMText) Then
                text_only = False
                Exit For
            End If
        Next child
    End If

    ' Process child nodes.
    If node.hasChildNodes Then
        ' Add a carriage return before the children.
        If Not text_only Then
            node.insertBefore _
                node.ownerDocument.createTextNode(vbCrLf), _
                node.FirstChild
        End If

        ' Format the children.
        For Each child In node.childNodes
            FormatXmlNode child, indent + 2
        Next child
    End If

    ' Format this element.
    If indent > 0 Then
        ' Indent before this element.
        node.parentNode.insertBefore _
            node.ownerDocument.createTextNode(Space$(indent)), _
            node

        ' Indent after the last child node.
        If Not text_only Then _
            node.appendChild _
                node.ownerDocument.createTextNode(Space$(indent))

        ' Add a carriage return after this node.
        If node.nextSibling Is Nothing Then
            node.parentNode.appendChild _
                node.ownerDocument.createTextNode(vbCrLf)
        Else
            node.parentNode.insertBefore _
                node.ownerDocument.createTextNode(vbCrLf), _
                node.nextSibling
        End If
    End If
End Sub

One of the projects I worked on was an embedded turbine controller that had an incredible amount of inherent complexity designed into the product. The controller used two processors, Motorola 68360 and MPC505, as well as dual-port memory for the inter-process communications (IPC). The developers spent a substantial amount of time installing the development software and trying to get the processors to communicate. This was the era where the software would be licensed to a NIC and so you could only compile the code on the machine with the licensed NIC. They even hired a consultant to help figure out how to make the processors talk.

I caught the tail-end of this project and it was not a pleasant beginning. My responsibility was to finish the PC configuration software for the controller after the high-priced consultant left. I had just moved over from another project so I had no exposure to this project. I found an incredibly complicated database schema and object-oriented software (C++/MFC-based) to go with the overly complicated controller! The consultant apparently had never heard of the KISS principle (where possible) and had created a behomoth. I had to dig through many, many classes and debug all of the classes to find why the database wouldn't save correctly. I found most of the time the consultant forgot to call the parent class constructor from the derived class constructor, although there were plenty of other bugs to fix.

One of the developers created an emulation program for the embedded controller that ran on a PC. The configuration software worked great with the emulation program, but would not talk to the actual embedded controller. It turned out it was a combination of structure packing and Endian problems. When the developers were trying to make the embedded processors talk, they chose a packing scheme that could not be natively duplicated by Windows or Visual Studio. The actual statement was:

#pragma pack(4,8)

Unfortunately I don't have the documentation handy, but as I recall it meant to align on 4-byte boundaries and align sub-structures on 8-byte boundaries. The key was the sub-structures and I couldn't get Visual Studio to duplicate the packing schema with the sub-structures without affecting the host structure as well.

"It seemed like a good idea at the time..." Here are the elaborate steps I took to solve the problem:

1. I ended up using some macros to extract the structures from the header files (there were lots and lots of structures) and put them into Excel.
2. I created the Excel spreadsheet and scripts to automatically calculate the size of the structure on the embedded platform and to look for "Endian" data types (mainly doubles and longs).
3. I created another Excel script to generate a C file that contained a string that contained the information to encode and decode the message. The script accounted for padding and endian conversion.
4. The conversion function was used in the emulator and the configuration software and worked successfully.

In hind sight, this solution was reasonable (since we didn't want to modify the embedded controller) but there is a very good reason you occassionally see:

WORD Reserved1;
WORD Reserved2;

throughout system header files. It is a very good idea to add manual packing variables rather than relying on the compiler to do it for you, especially if the software will be used cross-platform.