Click here to Skip to main content
14,669,112 members
Rate this:
Please Sign up or sign in to vote.
See more:
How to pass an array to SQL SERVER 2008 stored procedure and insert all the values of a array into a database table.

For Example: From ASP.NET C#, I would pass a parameter having multiple values separated by comma

as below

string category = "Cat1, Cat2, Cat3, Cat4";

I would like to insert all the values of above string in a table called Categories.

How could I achieve this using SQL SERVER 2008.
[no name] 3-Aug-13 11:08am
You start by doing some research
sam7one 4-Aug-13 2:45am
hmmm... thanks for answer else I would have never known about this option...GREAT HELP!
Zoltán Zörgő 4-Aug-13 6:10am
Anyway, google search is the option to start with. And it would have helped you - if you have had done it right.

Rate this:
Please Sign up or sign in to vote.

Solution 1

Try this:

1. Create a Type in SQL Server
CREATE TYPE dbo.MyDataTable -- you can be more speciifc here
  Category NVARCHAR(200)

2. Create a Stored Proc(on SQL Server) consume the above TYPE created and insert into Categories(assuming your table name is "Categories"
CREATE PROCEDURE dbo.InsertCategories
  @dt AS dbo.MyDataTable READONLY

  INSERT dbo.Categories(Category) 
  SELECT Category 
  FROM @dt 
  WHERE Category NOT IN (SELECT Category FROM dbo.Categories);

Now the c# part:

string category = "Cat1, Cat2, Cat3, Cat4";
string[] categories = category.Split(',');

DataTable dt_Categories = new DataTable();
dt_Categories.Columns.Add("Category", typeof(String));
DataRow workRow;
foreach(string cat in categories)
  workRow = dt_Categories.NewRow();
  workRow["Category"] = cat.Trim();
using (connectionObject)//assuming you have a connection here
    SqlCommand cmd = new SqlCommand("dbo.InsertCategories", connectionObject);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dt_Categories);
    tvparam.SqlDbType = SqlDbType.Structured;
sam7one 4-Aug-13 8:16am
THANKS, Exactly what I was looking for, My search ends here. Earlier, I found some articles about using Table Valued Parameters, but I was not able to figure out how to use it in application. This what I was looking for and working fine!
Kuthuparakkal 4-Aug-13 13:27pm
Very well, glad to know you were lookin for this!!!
sam7one 5-Aug-13 6:35am
Hi, in the procedure "InsertCategories" how could I check if specific category already exists in "Categories" TABLE?
Kuthuparakkal 5-Aug-13 7:04am
Updated soln, please check!
sam7one 12-Aug-13 9:42am
Thanks once again for answer...!
Kuthuparakkal 12-Aug-13 9:52am
No probs :)
AkibAli 20-Oct-16 4:29am
Nice Article
R.Akhlaghi 6-Apr-15 7:05am
u cAN FOUND another solution here
R.Akhlaghi 6-Apr-15 7:05am
Rate this:
Please Sign up or sign in to vote.

Solution 2

You have several possibilities.
Passing the values as a table valued parameter is a good option. There are other though, see my answer here: How to pass collection values to sql server 2008 Stored Procedures ?[^]
Besides what I have mentioned there, you can use a function like this[^] to split the string on t-sql side and have it as table.

But in your case, the simplest approach would be split it in c# and simply issue the insert statement multiple times. Don't complicate things!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100