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.
Posted
Comments
[no name] 3-Aug-13 11:08am
   
You start by doing some research http://www.google.com/search?q=How+to+pass+an+array+to+SQL+SERVER+2008+stored+procedure
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
as:
CREATE TYPE dbo.MyDataTable -- you can be more speciifc here
AS TABLE
(
  Category NVARCHAR(200)
);
GO

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
AS
BEGIN
  SET NOCOUNT ON;

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


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();
  dt_Categories.Rows.Add(workRow);
}
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;
    cmd.ExecuteNonQuery();
}
   
v3
Comments
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
   
http://stackoverflow.com/questions/29057464/how-to-process-an-integer-list-row-by-row-sent-to-a-procedure-in-sql-server-2008/29072035#29072035
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