I have a stored procedure which accepts various varchar parameters. The middle tier code which calls the procedure has not been consistent in terms of the values it submits. For example, sometimes a parameter like "Transport Description" will be submitted as NULL and sometimes it will be an empty string.
In terms of the client, an empty string simply means a value was not entered.
Should I convert all the empty varchar parameters to NULL before inserting the data?
I can't decide if this falls into the realm of a best-practice to preserve the integrity of the data or a bad idea because I'm not representing what was actually submitted to the database.
I should note that all inserts and updates are tracked in an audit log.
As a DBA, your sacred duty is to take whatever data was entrusted to you, and leave it as is.
A NULL in a field usually means "DATA was not entered" or "DATA is not applicable" (but can have other meanings - it is context specific), and may actually MEAN something to the business layer. An empty string may actually be what the user entered and WANTS. Of course, I am generalizing - possibly the reason you get either NULLs or '' (empty strings) is due to some sloppy coding in the business layer (let us hope not, though).
Either way, have a chat with whoever provides the Middle Tier and see what they are using NULL vs Empty String for - and then go from there. Maybe they are thinking that they always pass in an Empty String (or always NULLS) and are not aware of the randomness (in which case advise them on what you think should be the standard), or maybe NULL really is information to themTweet