dynamic sql in Cursor

a table named tbl_customerMaster

create table(customerID int, customerName varChar(50), Adderss varChar(100))

---

create procedure sp_saTest ( @.customerID varChar(20) )

AS

declare curCustomer cursor

for SELECT customerName, Adderss FROM tbl_customerMaster WHERE customerID IN (@.customerID)

open curCustomer

fatch....

....

xyz, xyz, close cursor

---

the sp created successfully, but when i try to execute that sp i found a error there

Exec sp_saTest (1,2,5,7,10)

Server: Msg 245, Level 16, State 1, Procedure sp_Customer
Syntax error converting the varchar value '1,2,5,7,10' to a column of data type int.

what is the problem there?

suppose when i fire as follow

SELECT customerName, Adderss FROM tbl_customerMaster WHERE customerID IN (1,2,5,7,10)

directly in it will be work properly, then why there is error with parameter name?

i m using SQL Server 2000.

The problem is that you're passing in a string, when it wants a list of numbers. I know the string happens to look like a list of numbers, but it's actually not one.

You could do something like:

WHERE ',' + @.customerID + ',' like '%,' + cast(customerID as varchar(20)) + ',%'

But this is kinda nasty. Another alternative is to split the string into a series of numbers. There is code for this at
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt

It's down near there end, an example of just this type of behaviour.

Hope this helps...

Rob