BlackM BlackM - 5 months ago 13
SQL Question

SQL - Query using XML Path

I have the following query:

SELECT ara.req_id
FROM tbl_ara ara
WHERE id in (
SELECT STUFF((
SELECT ',' + cast(id as varchar(8000))
FROM contact
WHERE ct_id = 2
FOR XML PATH('')
), 1, 1, ''))


So, theres is table with name
contact
that I want to get all the
ids
when they are type of
2
. Then I am creating a list with Stuff.
Then I want to use another table and get all the records that their
id
is on that list.
You can see the logic in the query.
The problem is that SQL doesn't recognise it as list and I get the following error:


Msg 245, Level 16, State 1, Line 11 Conversion failed when converting
the nvarchar value
'6019,49111,49112'
to data type int.

Answer

STUFF in your case returns character data. From the documentation (https://msdn.microsoft.com/en-us/library/ms188043.aspx):

Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

So you do not get a list of IDs but rather a string containing numbers and commas.

Is there a reason for not using the IDs directly?

SELECT 
    [ara].[req_id]
FROM [tbl_ara] [ara]
WHERE [id] IN
             (
              SELECT 
                  [id]
              FROM [contact]
              WHERE [ct_id] = 2
             )

EDIT: Of course the JOIN solution by @gofr1 is a pretty good option, too.