Sending Variable Values to Parameter In Stored Procedures.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

A. Shiraz - 04 Jul 2004 20:03 GMT

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.

Reply to this Message

Clint Colefax - 06 Jul 2004 01:00 GMT

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.

> I have scanned the various books on SQL Server and ASP .net but I cannot find anything on accomplishing the following :
>
[quoted text clipped - 20 lines]
>
> Thank you for your help and for reading.

Reply to this Message

William \(Bill\) Vaughn - 06 Jul 2004 17:42 GMT

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

Signature

____________________________________
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.
__________________________________

> I have done this before but not by using an array but by converting your multiple values into a comma delimited string, for example:
>
[quoted text clipped - 9 lines]
> >
> > - 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 :
> >
[quoted text clipped - 12 lines]
> >
> > 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.

Reply to this Message

Stu - 07 Jul 2004 23:09 GMT

<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)

Reply to this Message

Sending Variable Values to Parameter In Stored Procedures.

Free Magazines