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

Convert While loop to CTE (4h)

Multiple ordered aggregate functions in the same scope have mutually incompatible orderings (5h)

Beginner question on how to use a user defined function (7h)

Building dynamic columns not get last columns complete why? (1d)

Display the latest value (eg mobile no)from the active record of customers (1d)

Get data from remote server with only read access and save to local server (2d)

SQL query to update before instert (2d)

Query for sending emails for users (2d)

- Advertisement -