Conversion failed when converting the varchar value to data type int

i have this query and its work fine for me

SEL开发者_JAVA百科ECT STUFF(
(SELECT ','+SLT_SubListName  FROM sublists where SLT_SubListId in (1,2)  FOR XML PATH('')),1,1,'');

but when i change the in parameters (1,2) into the 'select SBS_SubListId from subscriber where SBS_SubscriberId=1' which also return the 1,2

SELECT STUFF(
(SELECT ','+SLT_SubListName  FROM sublists where SLT_SubListId in (select SBS_SubListId from subscriber where SBS_SubscriberId=1
)  FOR XML PATH('')),1,1,'');

its giving me the error which is the following

Conversion failed when converting the varchar value '1,2,4,5' to data type int.

if anybody needs i can also post my table schema here. thanks

you have to first split these comma sepered values on comma bases, and then apply converrsion.

I suspect tht your subquery is returning one entry of "1,2,4,5" which is not an integer. You need to get it to return 4 rows with one of these in each.

If you are doing this - show the results of the subquery - then you may have type differences.

Since your subquery seems to be returning the varchar '1,2,4,5', you can try the query using CONTAINS:

SELECT STUFF(
(SELECT ','+SLT_SubListName  FROM sublists where CONTAINS((select SBS_SubListId from subscriber where SBS_SubscriberId=1), SLT_SubListId 
)  FOR XML PATH('')),1,1,'');

That way it treats the results from the subquery as text.