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!

Related Articles Handling SQL Server Errors (5 April 2010) Testing with Profiler Custom Events and Database Snapshots (22 June 2009) Debugging Stored Procedures in Visual Studio 2005 (25 June 2007) Writing CLR Stored Procedures in C# - Returning Data (Part 2) (23 June 2005) Writing CLR Stored Procedures in C# - Introduction to C# (Part 1) (6 June 2005) Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005) An Evaluation of Stored Procedures for the .NET Developer (22 March 2004) Run CLR code from a stored procedure (14 October 2003)

Other Recent Forum Posts AlwaysOn AG + Replication maintenance - two scenarios to get the job done (2h) What happens in a dual LEFT OUTER join when the second join is NULL in both tables? (4h) How to set a variable from a table with comma? (1d) SSRS Expression IIF Zero then ... Got #Error (2d) Understanding 2 Left Joins in same query (2d) Use a C# SQLReader to input an SQL hierarchyid (3d) Translate into easier query/more understandable (3d) Aggregation view with Min and Max (3d)