William \(Bill\) Vaughn
7/6/2004 6:42:00 PM
No, this won't work. It's been tried many times. There are a number of
whitepapers out there that describe this problem and ways to get around it.
One innovative approach is to pass in a delimited string and use a SQL
function to parse the string and return a Table type object to use in the IN
expression.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Clint Colefax" <ClintColefax@discussions.microsoft.com> wrote in message
news:86CFFE2B-EC38-459E-8C52-573114F2E1F6@microsoft.com...
> I have done this before but not by using an array but by converting your
multiple values into a comma delimited string, for example:
>
> create procedure myProc(@vals VARCHAR(20))
> as
> SELECT *
> FROM myTable
> WHERE myCol IN ( @vals )
>
> As long as you ensure that your values are comma delimited (each seperated
by a comma) this should work fine.
>
>
>
> "A. Shiraz" wrote:
>
> > I have scanned the various books on SQL Server and ASP .net but I cannot
find anything on accomplishing the following :
> >
> > - how to send a list of variable values to an SQL stored procedure using
visual C# (studio 2003) and SQL Server 2000 Dev. as backend. Allow me to
illustrate :
> >
> > Suppose I have an sql statement that says :
> >
> > Select * from Activities where zip_code IN ("10023","10024"...."10029");
> >
> > Now if I have a stored procedure like so :
> >
> > Create Procedure activity_by_zip @zip varchar(1000)
> > As Select * from Activities where zip_code IN (@zip);
> >
> > Now I would like @zip parameter to be a list of zip codes.
> >
> > I get the zip codes in a dataset and I would like to use those zips to
search over the records as above.
> >
> > I searched the MSDN library and found allusions to SqlParameters[] but
there is not much usage information or examples. Am I on the right track?
> >
> > I cannot find any of these details in the ASP.net or Ado.net books out
there. It is simply this : what if I wanted to send a stored procedure an
array as a parameter such that the stored procedure could execute IN
operations on multiple values as so:
> >
> > Select * from Activities where zip_code IN ("10023","10024"...."10029");
> >
> > Thank you for your help and for reading.