SQL error converting varchar to int on string when selecting count as well

Question

I have a SQL query that is used to create a text file with a header row, detail rows, and finally a row with a 'X rows selected' message. Because it's for a report that uses a capital cedilla for the delimiter, I select the entire header row as a single string (truncated with an ellipsis to save space):

SELECT 
       'REC_TYPE'+CHAR(199)+'PLAN_ID'+CHAR(199)+...+'DATE_ANSWERED'
UNION ALL

SELECT  REC_TYPE+CHAR(199)+
        PLAN_ID+CHAR(199)+
        ...+
        Convert(char(10),DATE_ANSWERED,101)

FROM
       #My_Temp_Table  

UNION ALL

SELECT COUNT(*) + 'rows selected'

FROM
       #My_Temp_Table

If I'm not including the row count via SQL code, the whole thing works fine. For some reason, though, adding the row count line via union causes the query to throw a conversion error:

Conversion failed when converting the varchar value 'REC_TYPEÇPLAN_IDÇ...DATE_ANSWERED' to data type int.

What can I do to the row count part to prevent that error?

Answer 1

Because COUNT returns an int and then you are trying to add that to a string. Everything will attempt to be converted to an int because of datatype precendence.

Simply change your last query to something like this.

SELECT convert(varchar(10), COUNT(*)) + 'rows selected'