Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2015-03-16 : 16:52:58
|
| Hello All -Just a question. I need to create a stored proc for an Insert operation on a table that has 100 columns. Initially, the developer (for the app) wanted to just send me a an already constructed Insert statement. however, I wanted him to code the app to call the stored proc with separate parameters. Then I realized that the table has 100 columns. I don't think that this will be an issue, and I have some other stored procs that have 40 to 50 parameters, but mostly wondering what others think. I cannot come up with a different tactic, and not sure that i really need to.Thank you for any feedback. - will |
|
|
manub22
Starting Member
6 Posts |
Posted - 2015-03-17 : 03:02:47
|
| Rather than creating 100 parameters on the Stored Procedure you can:1. use Temp (#)Table before SP execution and use the same #Table within the SP, link: [url]http://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part3-by-using-table/[/url]2. Pass a Table variable as a param to the SP by using TVPs, link: [url]http://sqlwithmanoj.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/[/url]3. Or you can build an XML with all 100 columns and pass one XML varaible as a parameter, link: [url]http://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part2-by-passing-xml/[/url]SQLwithManoj.com |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2015-03-17 : 12:45:33
|
| Hi - Thanks for the suggestions. Passing in table variable sounds interesting. I will explore the various options, but I am constrained by the fact that the procedure will be used/called by a .Net app. That app will send the 100 parameters for each column.thanks - Will |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-03-18 : 08:53:41
|
Our INSERT stored procedures have one parameter for each column in the table, that way they are generic and can be used in any situation.I don't see any problem with 100 parameters to the SProc ...... whether the table design, of 100 columns in the table, is optimal would be a very different discussion  |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2015-03-18 : 11:54:27
|
quote:
Originally posted by Kristen
Our INSERT stored procedures have one parameter for each column in the table, that way they are generic and can be used in any situation.I don't see any problem with 100 parameters to the SProc ...... whether the table design, of 100 columns in the table, is optimal would be a very different discussion
Thanks for the feedback. Yes, 100 columns in a table....not something i was involved with. You know how that goes. :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-03-18 : 11:55:27
|
| There are 100 columns in tables here that I was involved with, so I've got no legs to stand on!! |
|
|
|
|
|
|
|
|
Stored Procedure with 100 parameters