Passing a CSV or Array to a Stored Procedure

By Bill Graziano on 8 September 2000 | Tags: Stored Procedures

Alex writes "I have a need to run a stored procedure which will insert multiple rows in the database. Biggest problem that I don't know from the begging how much inserts I need to do. I need to pass in the stored procedure an array of variables and loop over it. How I can pass complex datasets like arrays,lists, etc. in the stored procedure? How I can loop over that datasets in the stored procedure?" I've gotten this question a couple of times and here's how to do it.

Earlier I pointed people to another article that had code on how to combine different values together into a comma separated values field. I thought that would give people and idea how to do this. Unfortunately the two aren't as similar as I'd hoped. You can download the code for this procedure.

It is a stored procedure that takes two parameters @ARRAY and @SEPARATOR. It loops through the array variable and pulls out the values inside it. Right now the stored procedure just prints the values it finds. It's written so as to be easily customizable to do exactly what you need it to. Enjoy and happy parsing!