6

I'm adding features to an existing ASP.NET project. The code calls a stored procedure to add a row to a particular table. I need the option of also allowing one of the columns to have multiple values, in which case a row would be added for each value in that column.

I know I can call my insert method once for each row, but that sounds horribly inefficient. I know I could write multiple lines, delimited by semi-colons, and send them to the database in one request. But the existing code calls a stored procedure and so multiple insert statements would require I modify the heck of the existing code.

Note that the multiple values will be stored as multiple lines in a textbox, one line per value, and must obviously checked for being correctly entered.

Is there a simpler way to approach this?

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466

4 Answers4

2

SQL Server 2008 has the "table type" parameters that allows multiple rows as parameters. An example is defined in this SO question

SQL Server 2005+ has good XML handling. We use this currently for small datasets. SQL Server 2000 XML handling isn't as nice.

For all versions, you can create a temp table then call a stored proc that uses this table. You can use SQLBulkCopy to load the table. Useful for many rows.

Generally, the problem is a common one. Erland Sommarskog has an article "Arrays and Lists in SQL Server 2005 and Beyond" which is (one of) the definitive articles on the subject (he has more).

Summary:

  • table type (SQL Server 2008+)
  • XML (easier with SQL Server 2005+)
  • temp table (all versions)
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
0

Check out SqlBulkCopy, it will allow you to pass in your Datatable straight to the database.

Chenthil
  • 296
  • 4
  • 9
0

What about something like this:

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

Stolen from here.

If you are using SQL Server 2008 you can use this method:

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES('First' ,1)
VALUES('Second' ,2)
VALUES('Third' ,3)
VALUES('Fourth' ,4)

Not sure how you could get this to work with a stored proc through...

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • Thanks but I don't see this being compatible with a stored procedure. The code above knows ahead how many values to insert, and would therefore need to be performed on the client end. – Jonathan Wood Dec 28 '10 at 04:43
  • I don't think you can do what you want with a stored proc, unless you do `EXEC sp_MyProc` over and over again. You don't need to know the number of inserts that will be performed to use the above method if you use a loop to create the SQL statement that will be executed. If you do use a loop to create the SQL statement be VERY careful that you are not leaving yourself open to SQL Injection. – Abe Miessler Dec 28 '10 at 04:52
0

I ended up sending a delimited string as one of the stored procedure arguments, and then parsing out each element from the string and adding a row for each one.

I'm not sure this is the best way to accomplish this, but it didn't require a lot of reworking existing code and seems like it should be fairly efficient to me.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466