Issue not getting a result when stuff is used

I am using the stuff function to create an comma separated value

stuff works if I run it alone, but when I use in combination with another column to check against, it is failing.

Is it due to datatype issue?

Here is my code

and cast(verifyemails as varchar(max)) in (select STUFF((SELECT Distinct  ',' + '''' + cast(emails as varchar(max)) + ''''  from roleslist
left join users on users.fkuserid = roleslist.fkroleuserid  
where 
and fkUserID = 350 
group by emails 
FOR XML PATH('')), 1,1,'')

    
    

The above does not give the results, even the emails do exists in the table, but the below query is working if I run it alone. Does it have to anything with trim or anything?

select STUFF((SELECT Distinct  ',' + '''' + cast(emails as varchar(max)) + ''''  from roleslist
left join users on users.fkuserid = roleslist.fkroleuserid  
where 
and fkUserID = 350 
group by emails 
FOR XML PATH('')), 1,1,'')

Answer

If you’re doing an IN you shouldn’t bother creating a comma-delimited string. If you use a comma-delimited string you need to use LIKE instead of IN. Should be something like this…

and 
(select STUFF((SELECT Distinct  ',' + '''' + cast(emails as varchar(max)) + ''''  
 from roleslist
 left join users
     on users.fkuserid = roleslist.fkroleuserid  
 where fkUserID = 350 
 group by emails 
 FOR XML PATH('')), 1,1,'') LIKE '%' + cast(verifyemails as varchar(max)) + '%'

Or you could simply remove the comma-delimiting stuff and do this…

and cast(verifyemails as varchar(max)) in 
(select cast(emails as varchar(max)) from roleslist
 left join users
     on users.fkuserid = roleslist.fkroleuserid  
 where fkUserID = 350 
 group by emails)