Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Pass Array Values To Stored Procs

Posted on 2006-04-11
Medium Priority
Last Modified: 2010-04-23
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!
Question by:AnthonyMaritz
  • 2
LVL 35

Expert Comment

ID: 16429959
create an array of parameters

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

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
                     End If
                 End With
LVL 11

Accepted Solution

LordWabbit earned 800 total points
ID: 16431804
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)
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

Author Comment

ID: 16434141
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


 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)
exec 'select blah where blah in (' + strIn + ')'

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


LVL 35

Assisted Solution

YZlat earned 200 total points
ID: 16436725
I think you need to increase the size of strIn

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question