STRING_AGG() Error. Conversion failed when converting the varchar value to data type int

Question

I needed to convert a table insert/update trigger to a Service Broker task.

The INSERTED table is used but is passed from the Trigger as XML variable to the SP and executed asynchronously under the Broker Service. Again this is not the issue as I have the Trigger/SP/Broker working for mundane task like logging data to a log table.

Below is a block of code that was successfully executing within a Stored procedure and logging data to a log table.

INSERT INTO [dbo].[MapLocationLog](
    [lo_location]
    , [lo_Location_Code]
)
SELECT
    inserted.[lo_location]
    , inserted.lo_location_Code 
FROM (
    SELECT
        X.query('.').value('(row/lo_Location)[1]', 'int') AS lo_location
        , X.query('.').value('(row/lo_Location_Code)[1]', 'nvarchar(100)') AS lo_location_Code
    FROM @inserted.nodes('inserted/row') AS T(X)
) AS inserted

This is a modification that uses STRING_AGG() to create a delimited array of integers. Also logging successfully to a table. Note the change in type in the X.query for "lo_Location" changing from INT to NVARCHAR(10).

INSERT INTO [dbo].[MapLocationLog]([lo_Location_Code])
(
    SELECT String_agg(inserted.lo_location, ',')
    FROM (
        SELECT
            X.query('.').value('(row/lo_Location)[1]', 'nvarchar(10)') AS lo_location
        FROM @inserted.nodes('inserted/row') AS T(X)
    ) AS inserted
)

What I'm trying to do is get a comma delimited list of IDs and use these for subsequent query filtering.

This is where things go wrong.

DECLARE @ids NVARCHAR(max) 
SET @ids = (
    SELECT STRING_AGG(inserted.lo_location), ',')
    FROM (
        SELECT
            X.query('.').value('(row/lo_Location)[1]', 'nvarchar(10)') AS lo_location
        FROM @inserted.nodes('inserted/row') AS T(X)
    ) AS inserted
)

This is the error

Conversion failed when converting the nvarchar value '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20' to data type int.

The error line is on this SET @ids = (SELECT STRING_AGG(inserted.lo_location), ',')

I have been over this so many times and I cannot find where I'm going wrong. I've tried

CASTING
- CAST(inserted.lo_location, NVARCHAR(10))
- The whole Select statement

The only thing I can centre on is the STRING_AGG is explicitly NVARCHAR/VARCHAR and the Xpath value has implicit typing suggesting "1" is implicitly type as INT. I'm no expert on Xpath but that's my reading. What is confusing is where the code thinks I'm trying to convert to INT as there is no INT declarations.

Grateful for any insight.

`STRING_AGG` has nothing to do with yh error, it's where you're trying to convert a `varchar` to an `int`, not the other way round. — Thom A, May 25 '23 at 06:42
Are you 100% sure thats the line causing the error? Because reading it, it would appear to be a line, which you haven't shown, where you are trying to refence that variable in a where clause. — Dale K, May 25 '23 at 07:46
If you are 100% sure thats the case, then create a [mre] with sample data and desired results, which should be fairly easy as you can extract a single XML row and insert it as a constant in the query. — Dale K, May 25 '23 at 07:47
Include the whole code, the problem is probably (99.999%) that you somewhere want to do: where intColumn IN (@ids) which doesn't work because @ids is a single string, and not a tuple of values. — siggemannen, May 25 '23 at 08:12
if you're using dynamic sql, the line in the error point to the line in the dynamic code, not in your trigger code — siggemannen, May 25 '23 at 08:12
I don't think you're showing us the code you're actually executing. The error message suggests that you're doing something that reduces to this: `DECLARE @ids INT; SET @ids = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';` — AlwaysLearning, May 25 '23 at 08:40
"get a comma delimited list of IDs and use these for subsequent query filtering" This is a complete XY Problem: You don't actually need to aggregate anything. Just use `.nodes` and `.value` in a join to do whatever it is you want to do. If you show us the full procedure code we can help — Charlieface, May 25 '23 at 10:46
@DaleK, siggemanner and Charlieface were right. The issue was later in the code. It was where I applied the array that was, in fact the source of the error. While this issue has now been resolved I have another but that will be for another question. I apologise for not including the whole code set but it was long and convoluted. I apologise and lesson learned. Thanks to all. — Andye, May 28 '23 at 11:15

Power Mouse · Answer 1 · 2023-05-25T13:19:30.487

base on your example I created an xml and simplified your query if you can provide an original XML - that would be helpful

DECLARE @xml XML = '<inserted><row><lo_Location>123</lo_Location></row>'+
                    '<row><lo_Location>222</lo_Location></row>'+
                    '<row><lo_Location>123456789123456</lo_Location></row>'+
                    '<row><lo_Location></lo_Location></row>'+
                    '<row><lo_Location>aaa</lo_Location></row>'+
                    '<row><lo_Location>aaafgnhjfgy</lo_Location></row>'+
                    '<row></row>'+
                    '</inserted>'
DECLARE @out VARCHAR(100)

SELECT @out = STRING_AGG(i.lo_location, ',')
   FROM (
        SELECT
            X.query('.').value('(row/lo_Location)[1]', 'nvarchar(10)') AS lo_location
        FROM @xml.nodes('inserted/row') AS T(X)
   ) i

SELECT @out

result: 123,222,1234567891,,aaa,aaafgnhjfg

NOTE: in your first query you are using location as integer, in other queries you have nvarchar(10), which should "eat everything"... i would assume in table MapLocationLog column lo_Location_Code is NOT integer. In addition, TRY TO AVOID USING RESERVED WORDS. such as inserted/deleted, etc..

Thanks @powermouse. Your suggestion has helped clean up my code but as noted above, the error was where I used the aggregated list. Not the aggregation itself. Appreciate the assistance in pointing me in the right direction. — Andye, May 28 '23 at 11:21

STRING_AGG() Error. Conversion failed when converting the varchar value to data type int

1 Answers1