What is the best way to write a sp to insert a row having more than 200 columns?
in [Programming SQL]
|
From: peter on 12 Jul 2010 01:48 Hi, I have two tables having many columns. One has 215 columns and another has 95 columns. I am writing insert sp for these two tables. I think it is no good way to pass in 215 parameters into a insert sp. But what is the best way? Pass XML? Thanks. Peter
From: Erland Sommarskog on 12 Jul 2010 04:20 peter (petershaw8(a)hotmail.com) writes: > I have two tables having many columns. One has 215 columns and another has > 95 columns. I am writing insert sp for these two tables. > I think it is no good way to pass in 215 parameters into a insert sp. But > what is the best way? Pass XML? It's simply no good to have 215 columns in a table. :-( If you only want your procedure to insert a single row, there is little reason not to have 215 parameters. You are not likely to get less pain with XML - only more problems if you misspell name somewhere. Then again, if there is a need to insert many rows at a time, it is not effecient to call the procedure repeatedly in a loop. And it's not better with 215 parameters; there is a certain overhead per parameter. In that case it is better to pass an XML document which includes all the rows. But only if you are on SQL 2005 or earlier. If you are on SQL 2008 the obvious choice is a table-valued parameter. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: Index/Query Optimization Question Next: using insert with output |