General replacing of NULL values in SQL Views

Question

We have a fairly recent version of a SQL Server that we're using to extract data into a SAP BW Datawarehouse. We're using views to access data in tables on the SQL server. Some of the fields in these tables contain NULL values. These are transferred into SAP as String Value ('NULL') instead of empty, which causes us a major headache.

I understand that we can use COALESCE() in views to replace NULL values with a desired default value ('', 0, '1900-01-01', etc.), however, doing this for each NULL field that we encounter doesn't appear to be very smart.

Is there a better way of addressing this issue short of changing tables to not allow NULL values? Is it possible to include a custom global function that gets automatically applied to all fields fetched in a view without us having to call this function for each field individually?

Answer 1

@Jeroen Mostert's answer in the comments answers my question.

There is no global toggle, flag or setting that will magically eliminate NULLs for you. The closest thing is that COALESCE(, '') will "work" (for some values of "work") for almost every type (including DATETIME), with the notable exception of DECIMAL. You cannot write a function to do this, as functions in T-SQL cannot return different types based on their input. By far the best "fix" is indeed to fix the processing step, if only because ending up with 1900-01-01 dates in your database is typically quite undesirable.

Therefore, the only options are

1. go through each field that can potentially hold a NULL value and cleanse it within the view
2. handle NULL values on the receiving end (e.g. SAP BW); this could be done through a generic function to be placed in the entry layer's start routine or it could be done manually.

Disallowing NULL values on table level in the source system is in this case not feasible as we cannot change the application that writes to the tables (third party vendor ERP).