Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Tip/Trick

Passing a datatable to a Stored Procedure in SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
4.70/5 (11 votes)
25 Jun 2011CPOL 134.2K   20   6
Passing a datatable to a Stored Procedure in SQL Server 2008 which helps improve performance.

Create a table:


SQL
CREATE TABLE dbo.TestTableVariable
(
    ID        INT IDENTITY(1,1)    primary key,
    Name    VARCHAR(10),
    Addr    VARCHAR(10)
)

Create the table data type:


SQL
CREATE TYPE dbo.TableVariable AS TABLE
(
    ID        INT
)

Insert some default data:


SQL
INSERT INTO dbo.TestTableVariable VALUES ('aaa', 'addr-aaa')
INSERT INTO dbo.TestTableVariable VALUES ('bbb', 'addr-bbb')
INSERT INTO dbo.TestTableVariable VALUES ('ccc', 'addr-ccc')
INSERT INTO dbo.TestTableVariable VALUES ('ddd', 'addr-ddd')

Create the Stored Procedure that has the table variable as the parameter:


SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.SelectTestTableVariable
(
    @TableVar dbo.TableVariable READONLY
)
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT ID, Name, Addr FROM dbo.TestTableVariable
    WHERE ID IN (SELECT ID FROM @TableVar)
        
END
GO

Here is the C# code for a console application:


SQL
static void Main(string[] args)
{
    DataSet dataset = new DataSet();
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("ID");
    var dr = dataTable.NewRow();
    dr["ID"] = 1;
    dataTable.Rows.Add(dr);
    using (SqlConnection conn = new SqlConnection(
      @"Data Source=.\SqlExpress;Initial Catalog=Work;Integrated Security=True;"))
    {
        conn.Open();       
        using (SqlCommand cmd = new SqlCommand("[dbo].[SelectTestTableVariable]"))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.Parameters.AddWithValue("@TableVar", dataTable);
            using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
            {
                adp.Fill(dataset);
            }
        }
    }
}

License

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


Written By
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionGetting error when calling the SP using enterprise library Pin
Nitin S20-Nov-14 22:09
professionalNitin S20-Nov-14 22:09 
GeneralMy vote of 4 Pin
abhi_here7-Sep-13 19:35
abhi_here7-Sep-13 19:35 
GeneralThe select in the SP should be: SELECT ID, Name, Addr FROM d... Pin
vladsoti28-Jun-11 11:46
vladsoti28-Jun-11 11:46 
GeneralPerformance comparison for DataTable -> xml convertion? Pin
Burak Donbay28-Jun-11 2:05
Burak Donbay28-Jun-11 2:05 
GeneralReason for my vote of 5 Superb, new way, useful Pin
ramakrishnankt27-Jun-11 20:51
ramakrishnankt27-Jun-11 20:51 

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.