Post
Ask Question

Passing Table Valued Parameter to Stored Procedure: Part 1

Before SQL Server 2008, there were three options to pass bulk data into a stored procedure, but each had their own drawbacks.

  1. Pass each record one by one to stored procedure - this slows down entire process.
  2. BulkCopy - this works only for insert operations.
  3. Pass XML string - parsing XML string on SQL Server is very costly operation in terms of performance.
With the ability to accept table valued parameters, now you can pass structured type parameters to a SQL Server 2008 stored procedure from your .Net code. Table valued parameters allows passing entire sets of rows from ADO.Net code to SQL Server 2008.

In this demonstration, I am taking a simple scenario where I will pass customer information to a stored procedure and insert customer rows into the database. Here are the steps to implement passing a structure type to a stored procedure.

1. Create a CustomerDetail table in your database; this will be used to store Customer records.
  1. CREATE TABLE dbo.CustomerDetail(  
  2.       CustomerId INT IDENTITY(1,1) NOT NULL,  
  3.       FirstName VARCHAR(50),  
  4.       LastName VARCHAR(50),  
  5.       Address VARCHAR(100),  
  6.       Email  VARCHAR(100),  
  7.       Phone VARCHAR(10)  
  8. )  
2. Create a user defined table type which will be used as a table valued parameter in the stored procedure.
  1. CREATE TYPE dbo.CustomerDetailType AS TABLE(  
  2.    FirstName VARCHAR(50),  
  3.    LastName VARCHAR(50),  
  4.    Address VARCHAR(100),  
  5.    Email  VARCHAR(100),  
  6.    Phone VARCHAR(10)  
  7. )   

3. Create the SaveCustomerDetails stored procedure. This stored procedure will accept a table valued parameter. Note that the READONLY keyword is required to declare a table valued parameter; you cannot perform DML operations on a table valued parameter.

  1. CREATE PROCEDURE dbo.SaveCustomerDetail  
  2.    @CustDtl dbo.CustomerDetailType READONLY  
  3. AS  
  4. BEGIN  
  5.    SET NOCOUNT ON  
  6.      
  7.    INSERT INTO dbo.CustomerDetail (FirstName, LastName, Address, Email, Phone)  
  8.    SELECT FirstName, LastName, Address, Email, Phone FROM @CustDtl  
  9. END   
4. Write C# code to create a DataTable to be passed into the stored procedure and call the stored procedure.
 
In order to pass a structured type you need to create the DataTable with the same schema as the user defined table type has.
  1. DataTable custDT = new DataTable();    
  2. DataColumn col = null;    
  3. col = new DataColumn("FirstName");    
  4. custDT.Columns.Add(col);    
  5. col = new DataColumn("LastName");    
  6. custDT.Columns.Add(col);    
  7. col = new DataColumn("Address");    
  8. custDT.Columns.Add(col);    
  9. col = new DataColumn("Email");    
  10. custDT.Columns.Add(col);    
  11. col = new DataColumn("Phone");    
  12. custDT.Columns.Add(col);     
Pass structured type (DataTable) to the stored procedure.  
  1. SqlParameter param = new SqlParameter();    
  2. param.ParameterName = "CustDtl";    
  3. param.SqlDbType = SqlDbType.Structured;    
  4. param.Value = custDT;    
  5. param.Direction = ParameterDirection.Input;    
  6.     
  7. String dbConnStr =     ConfigurationManager.ConnectionStrings["DBConStr"].ConnectionString;    
  8. SqlConnection conn = null;    
  9.     
  10. using (conn = new SqlConnection(dbConnStr))    
  11. {    
  12.     SqlCommand sqlCmd = new SqlCommand("dbo.SaveCustomerDetail");    
  13.     conn.Open();    
  14.     sqlCmd.Connection = conn;    
  15.     sqlCmd.CommandType = CommandType.StoredProcedure;  
  16.     sqlCmd.Parameters.Add(param);  
  17.     sqlCmd.ExecuteNonQuery();    
  18. } 
Read more in Part 2 >>


Similar Articles