Pass Array Values To Stored Procs

Posted on 2006-04-11
Last Modified: 2010-04-23
Hi all

Im converting one of our applcations to , 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
    LVL 35

    Expert Comment

    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

    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

    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

    I think you need to increase the size of strIn

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now