Casting/Converting Chars to Varchars by UDF for SSRS

Question

The database which I report from most often is not properly typed. Almost every field, with the exception of some integers and datetimes, are CHAR fields. This can cause problems with SSRS layouts because some fields have tons of trailing spaces.

This causes layouts to be messy and such. I often rtrim these fields in my scripts to prevent this.

Is there any performance impact if I create a general UDF to automatically perform this on multiple fields in a single script?

Is one function RTRIM. CAST, Convert, etc preferable over the others?

Answer 1

I would not make a function to do this, just use CONVERT() on each improperly typed column in the result, something like this:

SELECT
    CONVERT(int,RowInt) AS RowInt
        ,RTRIM(CONVERT(varchar(15),RowString)) AS RowString
        ,CONVERT(datetime,RowDatetime) AS RowDatetime
        ,CONVERT(numeric(10,4),RowNumeric) AS RowNumeric
    FROM ...

this way, each column of the result set has the proper data type, and SSRS will know this and can apply formatting to them.

if this is a real chronic problem, and you find you are repeating the CONVERTs too much, you may want to just create some views that do this for you. just watch out making views of views of views.