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

Need Help in SQL query optimization (2d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (3d)

Suspect Parameter Sniffing? (3d)

See values of a proc when it is called from another proc? (4d)

Varchar() datatype and C# SqlDataReader truncates string (4d)

Find the extra rows (4d)

How to represent birth and death record in mysql db without duplicating data of an existing persons table existing persons table (5d)

EPR updates issues (5d)

- Advertisement -