MSSQL IN statement with comma-delimited returns

I have an SQL query that has a statement like

 where regionid in (tblpeople.region)

where the results from tblpeople.region could be something like 3913 or something like 3913, 3914.

It works just fine when it is a single variable but multiples gives me the error  Conversion failed when converting the varchar value '3913,3914' to data type int.    How do I go about fixing this?  
digitalwiseAsked:
Who is Participating?
 
lomo74Connect With a Mentor Commented:
select *
from yourtable
where exists (
select *
from tblpeople
where (',' + replace(tblpeople.region, ' ', '') + ',') like ('%,' + cast(yourtable.regionid as varchar) + ',%')
)

basically the operator becomes, eg.
',3913,3914,'  like  '%,3913,%'  which matches -> record extracted
0
 
CodebotCommented:
remove single quotes or place quote around each value.
3913,3914
or
'3913','3914'
0
 
Erick37Commented:
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))


0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
digitalwiseAuthor Commented:
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

Open in new window

0
 
lomo74Commented:
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

Open in new window

0
 
Erick37Commented:
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

Open in new window

0
 
Erick37Commented:
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
0
 
digitalwiseAuthor Commented:
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...    
0
 
digitalwiseAuthor Commented:
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 '.'.
0
 
digitalwiseAuthor Commented:
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

Open in new window

0
 
digitalwiseAuthor Commented:
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.
0
 
lomo74Commented:
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!
0
All Courses

From novice to tech pro — start learning today.