How to create dynamic parameters in SQL Server stored procedure

Question

I have a form that generate inputs dynamically, then I get their values and generate command parameters dynamically.

    SqlConnection con = new SqlConnection(cs);

    SqlCommand cmd =new SqlCommand("insert_op",con);
    cmd.CommandType = CommandType.StoredProcedure;

    for (int i = 0; i < Request.Form.Count; i++)
    {
        if (Request.Form["frm_option" + (i + 1)] != null)
        {

            cmd.Parameters.AddWithValue("@op" + i, Request.Form["frm_option" + (i + 1)]);
        }
    }
    try
    {
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
    catch
    {
    }

How can I pass these dynamic parameters to SQL Server stored procedure and how should stored procedure be?

Haseeb Asif · Accepted Answer · 2015-02-07 12:26:22Z

2

First of all, this isn't the right design.

To achieve your goal you need to pass multiple values in SQL statment as follows

SQL

INSERT INTO tbl_options(op_name) VALUES (value1),(value2),...(nthValue)

Code

Your code will be something like this

string command = "INSERT INTO tbl_options(op_name) VALUES";    
for (int i = 0; i < Request.Form.Count; i++)
                {                      
                    if (Request.Form["frm_option" + (i + 1)] != null)
                    {
                        command += "(@op" + i + "),";

                        cmd.Parameters.AddWithValue("@op" + i, Request.Form["frm_option" + (i + 1)]);
                    }
                }

edited Feb 7, 2015 at 12:26

answered Feb 7, 2015 at 12:20

Haseeb Asif

1,8062 gold badges25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike Over a year ago

how should stored procedure be?

Haseeb Asif Over a year ago

I would recommend to pass a single parameter to the SP and it should be csv list . (I am assuming that all the values are going to single column of the table).

Mike Over a year ago

But I want to insert each parameter into separate row.

Haseeb Asif Over a year ago

Yes, you will pass the csv list to stored procedure, then iterate that list to insert it into the table.

Community · Accepted Answer · 2017-05-23 12:13:23Z

1

If you want to "insert each parameter into separate row" then perhaps it is better to use table variable as a stored prosedure parameter?

Your User Defined Table Type and stored procedure might look like:

create type dbo.ValueTableType as table (Value varchar(255))
GO

create procedure dbo.InsertValues
    @Values dbo.ValueTableType readonly
as
begin

    insert into dbo.YourTable (Value)
    select Value from @Values;
end

See this ADO.NET example of how to initialize and pass a table parameter to stored procedure https://stackoverflow.com/a/10409710/623190

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Feb 8, 2015 at 15:01

Vadim Loboda

3,12129 silver badges47 bronze badges

Comments

Crowcoder · Accepted Answer · 2015-02-07 12:24:08Z

1

You can provide defaults for every field in the table, so any parameters you do not pass in will get the default. Here I demonstrate defaults of NULL.

CREATE PROCEDURE [dbo].[sproc_tbl_options_Insert] 
    @op_name nvarchar(50) = NULL,
    @op_somethingelse nvarchar(5) = NULL,
    @op_number int = NULL
AS
BEGIN
    INSERT INTO [tbl_options] (op_name, op_somethingelse, op_number)
    VALUES (@op_name, @op_somethingelse, @op_number);
END

answered Feb 7, 2015 at 12:24

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to create dynamic parameters in SQL Server stored procedure

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related