2

I'm exclusively using Stored Procedures when doing database operations with MySQL. There is a need to insert thousands of records periodically. Is there a way to pass a collection as a parameter to stored procedure? If there is, how does the stored procedure go about inserting the received parameter as one bulk insert?

jiminssy
  • 2,149
  • 6
  • 28
  • 45
  • 1
    Collections are, of course, a language construct in the application language you're using (C#, Java, etc). You didn't mention what language you're using. Have you ruled out using `LOAD DATA INFILE` to slurp your large datasets? – O. Jones Jul 03 '17 at 21:31
  • I am using Javascript. Maybe the use of the word "collection" was a poor choice. I didn't use it in the context of application languages but more so in a generic way. Regardless of the application languages, if I was to run raw SQL against MySQL to call SP, how would I go about passing it a collection of data and how should the SP be coded to insert a bulk load at once? This is really my question. – jiminssy Jul 03 '17 at 21:35
  • We can, please check my answer here: https://stackoverflow.com/a/72221646/4728084 – Bơ Loong A Nhứi May 12 '22 at 20:23

1 Answers1

1

My understanding of stored procedures is that the parameters cannot contain any code that will be executable commands like INSERT DELETE UPDATE only variables. So you cannot pass a whole INSERT comand to a stored procedure. Hence store procedures give protection from SQL injection. Here is the excellent and very Turing like explanation of the rationale for the separation of commands and parameters in stored procedures by @Polynomials https://security.stackexchange.com/questions/25684/how-can-i-explain-sql-injection-without-technical-jargon/25710#25710

So batch inserts will either be LOAD DATA INFILE as @O. Jones said in comments above or an INSERT stored procedure where you use the language of your choice to iterate the population of the parameters insert by insert.

Lew Perren
  • 1,209
  • 1
  • 10
  • 14
  • Thank you for the response. I wasn't leaning towards the literal SQL statements. I was wondering if there is a certain string format that the SP takes in as a parameter which would be interpreted as some kind of a collection in SQL. I guess there is no such thing. I will try doing the LOAD DATA INFILE approach. Thanks. – jiminssy Jul 03 '17 at 23:23
  • We can, please check this: https://stackoverflow.com/a/72221646/4728084 – Bơ Loong A Nhứi May 12 '22 at 20:23