Link to home
Start Free TrialLog in
Avatar of brettr
brettr

asked on

Filtering with WHERE IN

I have a varchar(8000) variable that I store a list of integers.  They are comma separated.  When I feed these into a WHERE IN, such as:

select *
from table1
WHERE col1 IN (@mylist)

I get this error:

Conversion failed when converting the varchar value '6,7,8,9' to data type int

This list is built dynamically using something such as:

select @mylist = coalesce(@mylist + ',', '') + cast(numberCol1 as nvarchar)
from table1...

Any suggestions how I get around the error?
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image


set @mylist = '(' + @mylist + ')'

select *
from table1
WHERE col1 IN @mylist
Sorry, i must be tired today

Thats not correct
Avatar of brettr
brettr

ASKER

that gives

incorrect syntax near @mylist
Create a function to get the numbers form the list as below

Then run the query as

select *
from table1
where col1 IN (select ID from dbo.fnSplit(@mylist))
create FUNCTION [dbo].fnSplit(@LIST VARCHAR(MAX))
RETURNS 
	@IDS TABLE(ID INTEGER)
AS
BEGIN	
	DECLARE @pos INT
	DECLARE @nextpos INT
	DECLARE @valuelen INT
		
	SELECT @pos = 0, @nextpos = 1
	WHILE @nextpos > 0
	BEGIN
		SELECT @nextpos = charindex(',', @LIST, @pos + 1)
		SELECT @valuelen = CASE 
					WHEN @nextpos > 0 THEN 
						@nextpos
                              		ELSE 
						len(@LIST) + 1
                    END - @pos - 1
		INSERT @IDS 
		VALUES (substring(@LIST, @pos + 1, @valuelen))
      	SELECT @pos = @nextpos
	END
	
	
	RETURN 
END

Open in new window

Avatar of brettr

ASKER

Actually looking for something much simpler.
SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brettr

ASKER

Thanks.  I'll probably go with that one.  Looks like it is working fine.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial