WHERE column IN (@param)

[aspnetguy]

NOTE: this is not done with any server side code but must be done from the Enterprise Manager or Query AnalyserI have an odd problem with my stored procedure. I want to pass into the procedure (in 1 parameter) a comma delimited list of ID numbers. The ID database field is type INTI know if the field type were VARCHAR I would do this

...WHERE ID IN ('1,2,3')

and if it is type INT I would do this

WHERE ID IN (1,2,3)

The problem the only way I can pass in the list of ids to the stored procedure is:

myProc '1,2,3'

and the only datatype I can get to accept that is a VARCHAR (@Param1 VARCHAR(255)) but this causing an error because the ID field is INT and it can't convert the @Param1 to INT.my code is

CREATE PROCEDURE myProc  @Param1 VARCHAR(255)AS  SELECT *  FROM myTable  WHERE ID IN (@Param1)

The specific error

Syntax error converting the varchar value '1,2,3' to a column of data type int.

Again ID is type INT and I have no control over this.

[jesh]

What if you created a table variable that would hold the IDs:

DECLARE @IDs TABLE(id int)

Then you could parse your @Param1 string one id at a time, cast them into ints and add them to the table variable. I'm still working on this step...Once you have your table variable filled with ids, you could run the query like so:

SELECT *  FROM myTable WHERE ID IN (SELECT id FROM @IDs)

Maybe you know how to parse a string in a stored procedure. If I find out I'll post it for you.EDIT: I found this link: http://vyaskn.tripod.com/passing_arrays_to..._procedures.htmMethods 2 and 3 on that page show how they convert a comma separated values parameter into individual ints that they store in a table variable. Let me know if it works, ok?

[aspnetguy]

I ended up using this function that I found on another forum

CREATE function CSVTable(@Str varchar(7000))returns @t table (numberval int, stringval varchar(100), DateVal datetime)asbegindeclare @i int;declare @c varchar(100);set @Str = @Str + ','set @i = 1;set @c = '';while @i <= len(@Str)beginif substring(@Str,@i,1) = ','begininsert into @tvalues (CASE WHEN isnumeric(@c)=1 THEN @c else Null END,rtrim(ltrim(@c)),CASE WHEN isdate(@c)=1 then @c else Null END)set @c = ''endelseset @c = @c + substring(@Str,@i,1)set @i = @i +1endreturnend

Then I just used

WHERE ID IN (SELECT numberval FROM CSVTable(@Param1))

Thanks for the helpCheers