What is better way to pass more than 30 parameters value to a stored procedure to insert values in table?

Question

Is there a better way to pass more than 35 paramteres to a SQL Server stored procedure?

I have a web page consisting of more than 35 input controls (textboxes, dropdown lists, date pickers), and I need to pass all those to the stored procedure to insert those values into a table.

I am using current basic approach as shown below:

cmd.Parameters.Add("@SplProjectNum1", SqlDbType.VarChar).Value = txtPromptNumber;
cmd.Parameters.Add("@SplProjectNum2", SqlDbType.VarChar).Value = ProjectClosureDate.ToString();
cmd.Parameters.Add("@SplProjectNum3", SqlDbType.VarChar).Value =  txtPromptNumber;            
cmd.Parameters.Add("@SplProjectNum4", SqlDbType.VarChar).Value = txtPromptNumber;
cmd.Parameters.Add("@SplProjectNum5", SqlDbType.VarChar).Value = txtPromptNumber;
cmd.Parameters.Add("@SplProjectNum6", SqlDbType.VarChar).Value = txtPromptNumber;
...

@Reniuz Correct, I too don't like the SP acceptng 30 parameters. That's why I am asking, is there any better way to do same? Please just guide. I'll do the rest. — Akshay, Jan 19 '17 at 09:42
What your procedure does? Just inserts all fields in one table? What kind of information you need to store in one table? How your page looks like with 30 inputs (I am thinking about user experience)? Can't you break inputs into smaller parts? — Renatas M., Jan 19 '17 at 09:51
@Reniuz Yes, it has insert statement inserting values. That page design is something that users want, just one submit button to insert all of them. — Akshay, Jan 19 '17 at 09:52
Page inputs = table sounds very bad design. I bet you can design DB structure much better. But you have what you have and table-value or xml approach can help you. — Renatas M., Jan 19 '17 at 10:02

score 1 · Accepted Answer · answered Jan 19 '17 at 09:52

Create a class with all the parameters are the properties in it, then create a object and assign the values to the properties. After that you can serialize the object to XML, and pass this XML to your stored procedure. In this method later you can add additional properties in your class, that will automatically add in the XML data you passed to the stored procedure, so there is no change in the parameter or signature of the stored procedure. Inside the stored procedure you can parse the XML data and fetch the required values.

Your stored procedure have only one parameter all time,

CREATE PROCEDURE SPABC_XYX ( @P_XML XML) ...

Looks good approach, will try and respond soon. – Akshay Jan 19 '17 at 09:55 — Akshay, Jan 19 '17 at 09:55

What is better way to pass more than 30 parameters value to a stored procedure to insert values in table?

1 Answers1