With IN Clause when Passing Collection as Parameter In Sql Server

Question

Query Gives O/p as

ItemSizeNM

(colName)

'U','V','X','Y'

But when I used this as input to IN Query in the Code Which I did. It Doesn't Gives same resultset. Why This Happens...? Table MstItemSize Has Proper Data.

 declare @tblRingSize table ( ringSize varchar(100))        
     declare  @ringSize varchar(100)         
     select   @ringSize= cast((otherringSize) as varchar) 
     from ##tempBand  where styleNo='BD00002';   

1. INSERT INTO @tblRingSize SELECT Item FROM dbo.SplitStrings_CTE (@ringSize, ',');

2. select ItemSizeNm from MstItemSize where SizeTypeNm ='Ring' and
   ItemSizeNm
   in --('U','V','X','Y')

   ( select Replace ( (select STUFF( (select ''',''' + ringSize from @tblRingSize For XML PATH('')),1,2,'') +'''' ) ,' ',''))

3. select Replace ( (select STUFF( (select ''',''' + ringSize from @tblRingSize For XML PATH('')),1,2,'') +'''' ) ,' ','')

Answer 1

You don't need to use STUFF, FOR XML, or REPLACE for the subquery:

select 
    ItemSizeNm 
from 
    MstItemSize 
where SizeTypeNm ='Ring' 
and ItemSizeNm  in (select ringSize from @tblRingSize)