Insert 150 columns in a single table using stored procedures

Question

We have a requirement to only use stored procedure to update/insert data in a SQL table.

I can create the stored procedure which will update the 150 columns, but that will require that I need to take all the 150 columns values from the object I have and explicitly pass all 150 columns to the stored procedure.

Can anyone please suggest a way in which I don't have to pass all 150 values explicitly ?

Answer 1

The other answers here have shown alternative ways to accomplish your goal. However, I would caution you against their use for several reasons.

1. Most are all brittle. Meaning that you have potential problems that will not show up compile time and, in many cases, won't show up unless every single edge case is properly tested.

2. Most result in the same or more code. You aren't saving any code by having to parse a string or turning an xml file into a temp table prior to updating/inserting into your main table.

3. The non-standard ways means that the next programmer on this project is going to need to pay attention to the fact that this procedure operates differently than the others for no other reason than to save a few lines of C# code.

Point is, just go ahead and create your stored procedure the usual way and call it the usual way. Most issues will show up in compile time or with limited test cases. Further by keeping the same format as your other code you are increasing the likelihood that future changes will be successful without a lot of extra work.