How to pass multiple parameters into a SQL Server stored procedure

Question

I am working on ASP.NET web application. In that I have one Edit Information page which has around 40 text-boxes. I need to update the data in the database, so I need to pass the values from all 40 fields into the database.

I am passing 40 parameters to a stored procedure to update data in the database. I need some other way to pass these parameters. Please suggest how to pass multiple parameters to a stored procedure.

Is there any other way to pass data or is it only possible, writing all in parameters list?

Regards, Vivek

What is the problem with adding 40 parameters? 40 lines of code is really not that bad, if you are feeling particularly lazy you can use the `sys.parameters` system table to make writing the code quicker: `SELECT 'command.Parameters.AddWithValue("' + Name + '", ' + STUFF(Name, 1, 1, '') + 'TextBox.Text);' FROM sys.parameters WHERE Object_ID = OBJECT_ID(N'dbo.YourProcedureName');` — GarethD, Oct 21 '13 at 15:17

score 1 · Answer 1 · edited May 23 '17 at 11:49

Forty parameters should not be a showstopper, but if you really want to pass fewer arguments to a stored procedure, you could consider passing an XML argument(s) that encapsulates the 40 values to the stored procedure - for example...

<booksdetail> 
  <isbn_13>800007890</isbn_13> 
  <isbn_10>07890B</isbn_10> 
  <image_url>http://www.whatever.com/Books/large/00/800007890.jpg</image_url> 
  <title>Some Title</title> 
  <description>The incredible story of....</description>
  -- ...
</booksdetail>

...passed to @bookDetail xml instead of the values therein passed to:

@isbn_13 varchar(13),
@isbn_10 varchar(10),
@image_url varchar(100),
@title varchar(100),
@description varchar(250),
-- ...

You would still have to account for the 40 values in the stored procedure, but if your preference is a slimmer stored-proc signature, this approach will achieve as much.

How to pass multiple parameters into a SQL Server stored procedure

1 Answers1