[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.aspnet.mobile

Sending Variable Values to Parameter In Stored Procedures.

A. Shiraz

7/4/2004 7:03:00 PM

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.
3 Answers

Clint Colefax

7/6/2004

0

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.

William \(Bill\) Vaughn

7/6/2004 4:43:00 PM

0

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.

anonid2

7/7/2004 10:09:00 PM

0

"William \(Bill\) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message news:<eE9UHh3YEHA.3512@TK2MSFTNGP12.phx.gbl>...
<snip>
> 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.

Yeah, I use this method and it works nicely. Something along the lines of:

CREATE TABLE #tmp (item)

/*

parse string parameter of form "a b c d e" - based on delimiter

(see handy routines: charindex, cast, ltrim, etc)

insert each item into #tmp

*/

-- now grab the data
SELECT *
FROM myTable
WHERE myField IN (SELECT item FROM #tmp)