Question
· May 13, 2020

SQL Server - How to write column with empty string instead of NULL

I have a class that writes to an external SQL Server database.  When the field is empty, it does not write the column and the column has the value NULL.  In some cases, I want the column to be the value of the empty string instead of NULL.

Class myRequestClass Extends Ens.Request
{

    Property MessageType As %String

}

The message that is passed to this class shows:

    <MessageType></MessageType>

Which writes the field as NULL with:

    tSQL = "insert into myTable (MessageType) values (?)"

    set tSC = ..Adapter.ExecuteUpdate(.tNumberOfRowsUpdated,tSQL,myRequestClass.MessageType)

I've tried setting to <MessageType>""</MessageType> as well as <MessageType>''</MessageType>.  But as expected, that sets the column to be quotations / apostrophes.

I've tried change the data value in the class to be "" before inserting the row:

     myRequestClass.MessageType = ""

but the column value is still NULL - not an empty string.

These also do not work - as the datalength of the column results in 1 not 0 (which is the length of an empty string):

    myRequestClass.MessageType = "''"
    myRequestClass.MessageType = """"

Creating the insert statement in the class as:

    tSQL = "insert into myTable (MessageType) values ('')"

Does seem to work, but I have numerous columns and various combinations.  It must work as a variable.

Discussion (5)0
Log in or sign up to continue

Very interesting!  I had not heard of that before.  In some preliminary testing, the datalength of the field is 4 (why not 1?).  Still investigating.

My other thought is to create a dynamic SQL to insert the fields.  First pass was to insert and then update based on if any fields were NULL.  But that lengthened my time from processing ~ 40k messages from 25 minutes to > 90 minutes.  Looking to see if I can build an array to track which fields needs to be inserted vs hard coded to ''.