create table main (ID int, Subcatid int, ContributorID int, LocationId int)
insert into main values (1,1,2,379),(2,2,2,379),(3,4,3,200),(4,32,2,379)
DECLARE @Subcatid VARCHAR(50)='1,2,32'
DECLARE @myXML AS XML = N'<h><r>' + REPLACE(@Subcatid, ',', '</r><r>') + '</r></h>'
Select * from main where Subcatid In (
SELECT Vals.id.value('.', 'INT') AS val
FROM @myXML.nodes('/H/r') AS Vals(id)
)
drop table main