Home > ASP.NET, SQL SERVER > Passing table valued parameters in SQL Server 2008 (User-Defined Table Types) from .NET

Passing table valued parameters in SQL Server 2008 (User-Defined Table Types) from .NET

Hi Friends,

*  Many times we come across a situation when instead of passing a single parameter value to the stored procedure we need to pass a bunch of values or you can say array of values.

* SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions. The table parameter feature can greatly ease the development process because you no longer need to worry about constructing and parsing XML data. The feature also allows the client-side developer to pass data tables from client-side code to the database as a simple parameter

* Lets see the syntax in SQL Server to create the Table Type.

CREATE TYPE [dbo].[baSelectionTableType] AS TABLE(
   
[VariableId] [int] NULL,
   
[SelectionId] [int] NULL,
   
[selectionDesc] [nvarchar](max) NULL,
   
[BxCodeValue] [nvarchar](max) NULL,
   
[EffectiveDate] [datetime] NULL,
   
[Status] [nchar](1) NULL
)
GO

 

after running above script you will find you table created in object explorer like:-

image

 

* Now your table type with defined columns has been created

* Now we will see how we can call the stored procedure containing parameter as the table type which we have created above

 

* suppose we have a stored procedure as follow :-

image

 

And we want to call this stored procedure from the Asp.net or simple c# code the code at the dot net  side will be as follows:-

 

 

 public void CallProc()
       
{
           
SqlCommand cmd = new SqlCommand();
           
DataTable dataTable = new DataTable();
           
dataTable.Columns.Add("LOB", typeof(Int32));

//Adding values to datatable
            object[] array = new object[1];
           
array[0] = 3;
           
dataTable.LoadDataRow(array, true);
            
           
array[0] = 4;
           
dataTable.LoadDataRow(array, true);

cmd.CommandText = "Sp_ForUserDefinedTableType";//proc name
            cmd.CommandType = CommandType.StoredProcedure;
           
cmd.Connection = con;
           
//passing datatable to Proc 
            SqlParameter spmLOB = cmd.Parameters.AddWithValue 

            (TableTypeParam", dataTable);
           
spmLOB.SqlDbType = SqlDbType.Structured;
           
con.Open();
           
SqlDataReader result = cmd.ExecuteReader();

}

In above function we are passing datatable to the StoredProcedure we mentioned above

and calling the same StoredProcedure.

 

Thank You.

Advertisements
Categories: ASP.NET, SQL SERVER
  1. Atul
    July 1, 2011 at 2:37 pm

    Gr8 going ganesh….. 🙂

  1. July 6, 2011 at 3:16 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: