SQL Server 2016 data type conversion error

Question

I have the below query. I am using multiple SELECT statements as the tool I am working with requires the FROM clause in the first line with a single SELECT value, for some reason.

DOCUMENT_ID is an integer type column. I need the DOCUMENT_IDs as a comma separated values, as the output from this query. (Ex. 1234,7786, 6478, 5835)

The problem is that I am running into the below error. Can someone please help.

Conversion failed when converting the varchar value ',' to data type int. I read some post regarding using converting to NVARCHAR using CAST, however I am running into syntax errors doing the same. Where exactly could it be applied?

SELECT DOCUMENT_ID FROM (
SELECT STUFF
(
    (
        SELECT ',' + em.DOCUMENT_ID 
        FROM(
            SELECT distinct a.DOCUMENT_ID AS DOCUMENT_ID FROM A_DOCUMENT a
            inner join PRICE_RECORD wk ON a.DOCUMENT_ID = wk.DOCUMENT_ID
            WHERE a.APPROVAL_STATUS = 'Submitted'
            ) em
        ORDER BY em.DOCUMENT_ID FOR XML PATH('')
    ),
     1, 1, '') AS DOCUMENT_ID
) AS Output

Thanks.

Larnu Larnu · Accepted Answer · 2019-02-03T11:06:44

If DOCUMENT_ID is an int then replace ',' + em.DOCUMENT_ID with one of the following:

CONCAT(',', em.DOCUMENT_ID) --SQL Server 2012+
',' + CONVERT(varchar(10),em.DOCUMENT_ID)
--Or, if you prefer CAST
',' + CAST(em.DOCUMENT_ID AS varchar(10))

int has a higher datatype precedence than a varchar, therefore your comma (',') was being implicity converted to an int, which was causing a conversion error.

SQL Server 2016 data type conversion error

1 Answers