MSSQL IN statement with comma-delimited returns

remove single quotes or place quote around each value.
3913,3914
or
'3913','3914'

Several ways:

1) create dynamic SQL so the actual query statement looks like this
'where regionid in (3913,3914)'

2) change the code so that the query selects each region id as a separate row
where regionid in (select regionid from sometable where ...)

3) create a table function which accepts your comma delimited string and splits it into rows:
where regionid in (select value from dbo.uf_YourSplit(tblpeople.region))

It is pulling the information from the actual table field and I don't want separate rows - the whole point is to pull the actual region names when the regionid is stored in the region field so that I can present the information back as what has been picked WITHOUT having to do a lot of extra queries.    This is the complete query.   I am using this in ColdFusion if that helps.

select firstname, lastname, title, personid, nl, dbo.GetProxy(tblpeople.entityid) as Proxy, 
reglist, (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where regionid in (tblpeople.region)
    FOR XML PATH('')) as regiontoshow,  (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where regionid in (tblpeople.chapter)
    FOR XML PATH('')) as chaptertoshow, chapter from tblpeople
	where companyid = 914815
	and peopletype = 2
    
    order by lastname

Select allOpen in new window

sorry I posted just before you submitted the full query.
here it is

select firstname, lastname, title, personid, nl, dbo.GetProxy(tblpeople.entityid) as Proxy, 
reglist, (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where exists (
		select *
		from tblpeople
		where (',' + replace(tblpeople.region, ' ', '') + ',') like ('%,' + cast(dt_regions.regionid as varchar) + ',%')
    )
    FOR XML PATH('')) as regiontoshow,  (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where regionid in (tblpeople.chapter)
    FOR XML PATH('')) as chaptertoshow, chapter from tblpeople
	where companyid = 914815
	and peopletype = 2
    
    order by lastname

Select allOpen in new window

What is the data type of your column tblpeople.region?

I'm assuming varchar or text since the result can be '3913, 3914'

In which case use the split user function:

select
      firstname,
      lastname,
      title,
      personid,
      nl,
      dbo.GetProxy(tblpeople.entityid) as Proxy,
      reglist,
      (SELECT regionname + ', ' AS [text()]
            FROM   dt_regions
            where regionid in (dbo.uf_Split2Int(tblpeople.region,','))
            FOR XML PATH('')) as regiontoshow,  
      (SELECT regionname + ', ' AS [text()]
            FROM   dt_regions
            where regionid in (dbo.uf_Split2Int(tblpeople.chapter,','))
            FOR XML PATH('')) as chaptertoshow,
      chapter
from tblpeople
where companyid = 914815
and peopletype = 2
order by lastname

CREATE FUNCTION [dbo].[uf_Split2Int] 
(
	@cList nvarchar(2000)
	,@cDelimiter nvarchar(5)
)  
RETURNS @SplitTable TABLE 
(
	nFieldValue int
) 
AS  
BEGIN 
	WHILE (CHARINDEX( @cDelimiter, @cList ) > 0)
	BEGIN
		INSERT INTO @SplitTable(nFieldValue)
		SELECT 
			CAST(LTRIM(RTRIM(SUBSTRING( @cList, 1, CHARINDEX( @cDelimiter, @cList ) - 1 ))) as int)

		SET @cList = SUBSTRING( @cList, CHARINDEX( @cDelimiter, @cList ) + LEN( @cDelimiter ), LEN(@cList) )
	END

	INSERT INTO @SplitTable (nFieldValue)
	SELECT LTRIM(RTRIM(@cList))

	RETURN
END

GO

Select allOpen in new window

Correction: forgot to add the 'select nFieldValue from'

select
      firstname,
      lastname,
      title,
      personid,
      nl,
      dbo.GetProxy(tblpeople.entityid) as Proxy,
      reglist,
      (SELECT regionname + ', ' AS [text()]
            FROM   dt_regions
            where regionid in (select nFieldValue from dbo.uf_Split2Int(tblpeople.region,','))
            FOR XML PATH('')) as regiontoshow,  
      (SELECT regionname + ', ' AS [text()]
            FROM   dt_regions
            where regionid in (select nFieldValue from dbo.uf_Split2Int(tblpeople.chapter,','))
            FOR XML PATH('')) as chaptertoshow,
      chapter
from tblpeople
where companyid = 914815
and peopletype = 2
order by lastname

lomo74 - this pulls back ALL of the region names from the region table and not just the ones that are in the person's record...    

erick - I get a Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '.'.

OK - lomo74 got pretty close - I had to do the same thing for the chapter but it worked (your first version)

select firstname, lastname, title, personid, nl, dbo.GetProxy(tblpeople.entityid) as Proxy, 
reglist, (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
   where (',' + replace(tblpeople.region, ' ', '') + ',') 
   like ('%,' + cast(dt_regions.regionid as varchar) + ',%')

    FOR XML PATH('')) as regiontoshow,  (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where (',' + replace(tblpeople.chapter, ' ', '') + ',') 
   like ('%,' + cast(dt_regions.regionid as varchar) + ',%')
    FOR XML PATH('')) as chaptertoshow, chapter from tblpeople
	where companyid = 914815
	and peopletype = 2
    
    order by lastname

Select allOpen in new window

I added it to my code - the "full solution" didn't work but I was able to apply it properly to the piece i needed.

sorry for delay I went to bed :-)
glad to hear you solved the second problem (the chapter) on your own.
hope you can fix the full solution now!