MSSQL STRING_AGG() Conversion failed when converting the varchar value to int.

Question

MSSQL STRING_AGG() Conversion failed when converting the varchar value to int.

andyewx

Old Hand

Points: 347
More actions
May 25, 2023 at 6:36 am

#4197125
Short story, I needed to convert a table insert/update trigger to a Service Broker task. That's not the issue but it might help explain the structures being used.
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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16041 More actions · Answer 1

does it work if you try

DECLARE @ids NVARCHAR(max)
select @ids = string_agg(inserted.[lo_location], ',')
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

andyewx Old Hand Points: 347 More actions · Answer 2

Thanks Frederico. Your code is good but it appears the error was later in my code. Apologies for not posting the whole code set but it was long and convoluted. Thanks for your input.