1

I am writing a c++ program using ADO's command and would like to pass multiple records to a SQL Server 2008 stored procedure at the same time. Is this possible? How would I do this?

A record might have multiple values to it. For example a record is worker (name, number, startDate)

user255377
  • 35
  • 8
  • I have seen this done by building an insert statement and passing that text to the SP. One insert statement with many values. – Joe C Dec 13 '17 at 17:10
  • I would prefer not to have to build a really long query. I was hoping to pass the data and then have the stored procedure do all the work. – user255377 Dec 13 '17 at 17:34
  • 1
    You can pass in a long string with delimiters. I have seen posts that mention a table valued parameter. The long query approach is simple and allows a 'bulk insert' rather than multiple single inserts (built by the SP) which has a big impact on performance. – Joe C Dec 13 '17 at 17:49
  • Here is a post that describes the table parameter solution - https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code – Joe C Dec 13 '17 at 17:50
  • So that post is using ADO.net. Where I am currently using classic ADO and C++. Wouldn't passing in a long insert statement, possibly 500 records, take a while. – user255377 Dec 13 '17 at 18:01
  • I don't think ADO can do this, at least not as efficiently as ODBC or OLE/DB (where you can submit multiple parameter sets in a single operation). And while it's possible to get ADO interfaces from OLE/DB I don't think the reverse is true. ADO was meant for pre .net visual basic and is thus fairly limited. – SoronelHaetir Dec 13 '17 at 18:18
  • Passing in long strings is faster than individual inserts. My preference would be to put business logic in the application and use SqlBulkInsert object. Have not used C++ in 15 years, not sure if you can call .Net DLLS or if SqlBulkInsert is available outside .Net. Another option would be to create a text file and use command line BulkInsert. – Joe C Dec 13 '17 at 20:39

0 Answers0