Stored Procedure to handle mulitple insert parameters send by webform

Posted on 2008-11-08
Last Modified: 2012-05-05
Hi Experts,

I have an web form that passes a variable amount of parameters to insert into DB tables

It could be 2 parameters or one or 10 depending on the user

How do i declare the parameters in a stored procedure to accept these input parameters?


The web form asks users to enter contact names.. if more names are needed, dynamic textbox controls are created via button click

each contact name is added seperatly to an sqldatasource via insertparameters and it calls the sp to insert the values into the db

for the sp, i dont want to create 20 input parameters to cover all cases, say the user enters ten contact names(via ten seperate textboxes) on the webform

Create PROCEDURE test

@business_name varchar(50),
@business_code varchar(3),
@contact_name1 varchar(30),
@contact_name2 varchar(30),
@contact_name3 varchar(30),
@contact_name4 varchar(30),
@contact_name5 varchar(30),
@contact_name6 varchar(30),
@contact_name20 varchar(30)

should the multiple values be sent differently to the stored procedure?
Question by:miguel_j
    LVL 7

    Expert Comment

    LVL 26

    Expert Comment

    by:Anurag Thakur
    in sql stored procedures there is one beautiful thing and that is setting the default values to the parameters

    @business_name varchar(50) = '',
    @business_code varchar(3) = '',
    @contact_name1 varchar(30) = '',
    @contact_name2 varchar(30) = '',
    @contact_name3 varchar(30) = '',
    @contact_name4 varchar(30) = '',
    @contact_name5 varchar(30) = '',
    @contact_name6 varchar(30) = ''

    now while calling the stored procedure you can supply only the patameters where you have the value and rest of the parameters will be initilized with the default values defined

    the following link further defines default parameters in sql
    (you might have to register on the web site)

    Author Comment

    thank you for your help

    So i do need to create many parameters to cover (not knowing how many times the user adds extra web controls..

    i was wanting to create sp parameters based on how many parameters was sent from the web form? it will vary

    say the user may enter 50 names? then taking those 50 names and adding them into a seperate rows using a loop?
    LVL 7

    Accepted Solution

    One other option (if you know that the values will only be strings) is to "serialize" the values into a single string, for example by Joining them with ';' as delimiter. Then splitting the value in the sproc (

    String[] values = new[] {"one", "two", "three"};
    String serString = String.Join(";", values);

    Open in new window

    LVL 26

    Assisted Solution

    by:Anurag Thakur
    i did not completely got your question previously

    the best solution in this case should be passing and XML with object to the stored procedure and then reading the XML in the database and then looping it around and inserting or updating the multiple values to the database


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now