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?

Ephraim Wangoya

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

select *
from table1
WHERE col1 IN @mylist

Ephraim Wangoya

Sorry, i must be tired today

Thats not correct

brettr

ASKER

that gives

incorrect syntax near @mylist

Ephraim Wangoya

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

brettr

ASKER

Actually looking for something much simpler.

SOLUTION

Ephraim Wangoya

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

brettr

ASKER