Sending Variable Values to Parameter In Stored Procedures.

A

A. Shiraz

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.
 
C

Clint Colefax

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.
 
W

William \(Bill\) Vaughn

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 said:
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.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:
 
S

Stu

William \(Bill\) Vaughn said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top