Click here to Skip to main content
15,355,301 members
Articles / Database Development / SQL Server
Posted 23 Dec 2007


30 bookmarked

SQL Server 2008 Table Valued Parameters

Rate me:
Please Sign up or sign in to vote.
3.44/5 (11 votes)
23 Dec 2007CPOL1 min read
Using SQL Server 2008 table valued parameters to insert multiple records (VS 2008).


The last two weeks, I have been working on developing bulk data manipulations. And even before that, I always wondered “What's the best way to pass an array of values into a SQL Server Stored Procedure?” One option I found was OpenXML, which I blogged about here.

I am trying out Visual Studio 2008 and SQL Server 2008 these days. And, last night, I found out that SQL Server now supports table valued parameters which allow us to send data tables as parameters to Stored Procedures. It still uses the same ADO.NET API.

How it works

Now I’m going to show how it works. For this, I’m using the same scenario which I used in my previous post (reading contents of a folder and saving their information). Here, I have used the SQL Server 2008 November CTP and the Visual C# 2008 Express edition.

First, create the table given below:

FileName varchar(50) PRIMARY KEY,
CreatedDate varchar(50) ,
Size  decimal(18, 0) )  

Then, we should declare a new table User Defined Type in the database:

create type FileDetailsType as table
    FileName        varchar(50),
    CreatedDate        varchar(50),
    Size       decimal(18,0)

Then, create a Stored Procedure that gets a parameter of the above type, and insert several rows in a single command.

create procedure InsertFileDetails
    @FileDetails FileDetailsType readonly

insert into  
    FileDetails (FileName, CreatedDate, Size)
        select FileName, CreatedDate, Size

To execute this procedure, we can create a data table and add the rows into it. Then pass this data table as a parameter to the database.

private static void SaveFileDetail(List<FileInfo> info)
    Console.WriteLine("**********updating with tablevalued parameters****");
    DataTable dt = preparedatatable();
    foreach (FileInfo file in info)
        DataRow dr = dt.NewRow();
        dr[0] = file.Name;
        dr[1] = file.CreationTime.ToShortDateString();
        dr[2] = (decimal)file.Length;

    using(SqlConnection conn = new SqlConnection("your connection string"))
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "dbo.InsertFileDetails";
        SqlParameter param = cmd.Parameters.AddWithValue("@FileDetails", dt);
        Console.WriteLine("Completed Updating the database");

Now that’s cool, isn’t it?


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


About the Author

Software Developer
Sri Lanka Sri Lanka
i work for as a c# software developer, and experienced with c# 2.0/3.0,,, sqlserver,mysql, xml, xsl,

Comments and Discussions

GeneralMy vote of 3 Pin
Gyozo Kudor10-Jan-17 20:34
MemberGyozo Kudor10-Jan-17 20:34 
QuestionHandy Pin
OriginalGriff3-Dec-13 5:45
mveOriginalGriff3-Dec-13 5:45 
GeneralExcellent Pin
Dante Sparda31-May-10 8:19
MemberDante Sparda31-May-10 8:19 
GeneralThanks Pin
DavidLieu9-Dec-09 11:49
MemberDavidLieu9-Dec-09 11:49 
Generalgood.. [modified] Pin
Niladri_Biswas29-Sep-09 2:01
MemberNiladri_Biswas29-Sep-09 2:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.