Question
Jo Ellen Laansma · 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.

 

00
0 5 1,004 2

Replies

When I set

    myRequestClass.MessageType = $char(0)

and then insert with ? parameter, it still has datalength of 1.

Hi Jo Ellen,

Can you try to change your SQL query into

INSERT INTO myTable (MessageType) values (?)
SELECT ISNULL(?, '') FROM myTable

I have not tested this but from what I read about ISNULL(), this might work. If your MessageType  field is NULL (I assume that is your default) it should return '' which might parametrize your query...

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 ''.

After further testing, I realized my example field was not varchar but char( 4) (hence length 4).  But a quick test on another field proved it out with datalength of 0.

THANK YOU VERY MUCH for this SQL tip!