Passing to an IN in a MSSQL Stored Procedure

[JustJon]

I'm working on a query where there is an IN in the SQL clause of the stored procedure, but I'm not sure how to pass in the data.

As an example of what I've written:
CREATE PROC example_procedure(
@mon int
,@num int
,@list varchar(1000))
as

select *
from the_table
where number = num and month = @mon
AND customer IN (@list)
group by date,total_users
order by date
RETURN
GO

But I get an error from @list because it percieves the list variable as more than one variable.

[txbakers]

Somehow you need to concatenate all the list items (if they are strings) as follows:

@list = "'item1','item2','item3'"

You alredy provided for the () in the SP, so you wouldn't need that in your list.

But you do need the single quotes and the commas.

[aspdaddy]

You need to use dynamic SQL or a UDF Split function to do that. Neither is a good solution, if you search SQLTeam there are some articles on it.

Some solutions here too:
Passing Arrays to Stored Procedures [vyaskn.tripod.com]

[edited by: aspdaddy at 4:36 pm (utc) on Mar. 27, 2006]

[JustJon]

As a sample of the data I was passing in:
'22', '2387', '1002145', '2005035', '2006995', '2009880', '3011605', '3012258', '3021298', '3024088', '3525330', '4026086', '4026274', '4027858', '4031010', '4033534', '4033535', '4033946', '4034735', '4035907', '4038569', '4041019', '4041044'

But this gives me the following error (error message copied from PHP execution):
Warning: sybase_query(): Sybase: Server message: Procedure or function example_procedure has too many arguments specified.

[syber]

CREATE PROC example_procedure(
@mon int
,@num int
,@list varchar(1000))
as

EXECUTE ('select * from the_table where
number = ' + CONVERT(VARCHAR(10),@num) + 'and month = ' + CONVERT(VARCHAR(2),@mon) +
'AND customer IN (' + @list + ')group by date,total_users order by date ')

RETURN
GO