Passing a list/array to an SQL Server stored procedure 2005

Hi, I m using sql 2005 as a back end in my application...

I am useing Store procedure..for my data in grid..

ALTERPROCEDURE [dbo].[ProductZoneSearct]

(

@.Productidchar(8),
@.Pronamechar(8),
@.radiusint,
@.modevarchar(5)='M',
@.Zonenvarchar(1000),

)

AS
SETNOCOUNTON;
Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))

Insert #Product
Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment
,Miles, UserID, Daily, Phone
From [tblproduct]
Where city IN (@.Zone)

Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment
,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone
From #Product As L
Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID
Order By DateAvailable

if i pass value in"where city in (@.Zone)" and@.Zone ='CA','AD','MH' then it can not get any result..but if writewhere city in ('CA','AD','MH') then it give me perfact result..

I tried to below syntax also but in no any user
Where city IN ('+@.Zone+')

In short if i pass value through varibale (@.Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ?

Please Hel[p me !!!

Thank you !!!

Check out this blog post:

Passing lists to SQL Server 2005 with XML Parameters

|||

Hmmm... you may be better off writing an object data source and filtering the data after the select. Otherwise, have a look at sp_executesql or somesuch...

|||

Problem is If i pass only one value into variable then it gives me result but if i pass more than one value then it wount give me result.
Example..If i pass @.Zone='KS' then it works fine but if i pass @.Zone='KS','MS' Then it wong give me data...coz "," (comma) .seperated..may be it count after comma seprateion its a new value...or i dont know why i m not getting result

Please help me

Thank you & Regards.

|||

Sorry I am not getting you..:-((

|||

Instead of passing the parameters to the stored procedure, you can just select everything, and filter the data after. Look at FilterParameters and FilterExpression for SQL DataSource. I think you can set the FilterExpression="WHERE Region IN (@.Region)", and add @.Region as a FilterParameter, or something like that. Otherwise, you can make an ObjectDataSource that executes the stored procedure, and you can filter the resultset inside there however you want.

EDIT

Well, the whole Filter parameter thing with an "IN" clause isn't working for me... however, I think you can handle the datasource's filtering event to build the filter expression.