Pass Array Values To Stored Procs

Hi all

Im converting one of our applcations to vb.net , the old app uses in line T-SQL to build its qeuries and then run it against SQL database. One of these functions uses a select statement with the IN clause example:  Select * from [Table] where [Field] In (value,value2,value3........

How do i convert this to using a stored procedure. Can i pass the comma seperated values as a parameter and then exec the query?

thanks in advance!
AnthonyMaritzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
create an array of parameters

Dim parms(count) As SqlParameter
For n=0 To count-1
     parms(n) = New SqlParameter(arrSqlParams(n), arrValues(n))
Next


add the parameters to a stored procedure:
           Dim cmd As SqlCommand = New SqlCommand("stored procedure name", conn)

             ' Configure the SqlCommand object
         
                 With cmd
                     .Connection = conn
                     .CommandType = CommandType.StoredProcedure      ''Set type to StoredProcedure
                     .CommandText = procName                    ''Specify stored procedure to run

                     '' Clear any previous parameters from the Command object
                     Call .Parameters.Clear()

                     '' Loop through parameter collection adding parameters to the command object
                     If Not (parms Is Nothing) Then
                         For Each sqlParm As SqlParameter In parms
                             cmd.Parameters.Add(sqlParm)
                         Next
                     End If
                 End With
0
LordWabbitCommented:
you might want to look at creating 'in line sql' in your stored proc, although the entire benifit of a stored proc is lost since sql server can't optimize the sql code.  you can pass the value of in clause as a paremeter and then use
create procedure Mine
declare strIn varchar(255)
as
exec 'select blah where blah in (' + strIn + ')'

The another option is to  place the elements in the sql in statement in a loop and hit the database for each element, not exactly a good idea but it would all depend on the number of hits, and the database structure
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnthonyMaritzAuthor Commented:
thanks for help.

I have a List box which can contains up to about 200 institution names ,they are identified via tag ( on startup n pass the Primary Key of that row to the list item’s tag property) , the problem is im using GUID's as primary keys, meaning that if i select all the institutions in my list box the resulting parameter could be very long

example

 strIn  = ({90FF3246-CC6E-4a9a-B8AE-27AF108DDD9E},{97DBE439-2376-49a7-AE1D-50320A460C28},{00B8E020-621A-4487-B479-8C0CDCC23947}..........

so then

create procedure Mine
declare strIn varchar(4000)
as
exec 'select blah where blah in (' + strIn + ')'

my question then , at some point would i be limited with the parameter length???

thanks



0
YZlatCommented:
I think you need to increase the size of strIn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.