Passing mutile integer value to stored procedure as string 1

[sqlmann]

Hi


I Want to pass multiple integer value to stored procedure. so i thought i can pass it as string value like '10,20,30' to procedure.

Select * from customer where cusid in(@custlist).

But it's not possile variable value in paramter.
Error as
---
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '10,20,30' to data type int
---

Is there any other way to achive it...?



Please suggest me

 

[RyanEK]

There are several methods to achieve this. See here Passing list/array to an SP

Ryan

 

[sqlmann]

Thank you very much

 

[jrbarnett]

Another way:

Set up a table for temporary storage (not a "temp table" as in starting with a # character).

Columns include:

application login ID
value

composite primary key of both tables.

When your users choose their options,
firstly delete the contents of this table where the login id matches, then a row gets inserted into the temporary table for each with their login id thus:

loginid, 10
loginid, 20
loginid, 30

then its a matter of a simple inner join matching the loginid to them and the id there to its source data.

The reason you put the login ID there is so that it is multi user - ie you won't get multiple users selections if two people use the application simultaneously.

John