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
– GarethD, Commented Oct 21, 2013 at 15:17

Community · Accepted Answer · 2017-05-23 11:49:25Z

1

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.

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Oct 22, 2013 at 1:54

J0e3gan

8,96810 gold badges58 silver badges81 bronze badges

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

Collectives™ on Stack Overflow

How to pass multiple parameters into a SQL Server stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related