Table-Valued Parameters (TVP) in SQL Server:

1. Create Type [TVP_Int]:


USE [DB01]
GO

CREATE TYPE [dbo].[TVP_Int] AS TABLE(
    [Val] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Val] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)
GO

 

SSMS : programmability => Types => User-Defined Table Types;

 
2. Stored Procedure:


CREATE PROCEDURE [dbo].[List_TestInfo] 
  @IDs As TVP_Int Readonly

AS
BEGIN

  SET NOCOUNT ON;

  SELECT 
    ID, Name 
  FROM
    TestInfo WITH(NOLOCK) 
  WHERE 
    ID IN (SELECT Val FROM @IDs)

END

 

3. Test SP:


DECLARE @Tbl AS [TVP_Int];
INSERT INTO @Tbl (Val) VALUES (1001)
INSERT INTO @Tbl (Val) VALUES (1002)
INSERT INTO @Tbl (Val) VALUES (1003)

EXEC [List_TestInfo]
    @IDs = @Tbl 

 

4. (C#) Convert SqlParameter (List to DataTable):


private DataTable ToDataTable<T>(List<T> list)
{
  DataTable t = new DataTable();
  t.Columns.Add("Val", typeof(T));

  foreach (var i in list)
  {
    t.Rows.Add(i);
  }

  return t;
}

 

Reference:

1) SQL 2008 TVP資料匯入之火力展示 https://blog.darkthread.net/blog/import-with-sql-tvp
2) SqlBulkCopy Vs. TVP Vs. XML Parameter Vs. Individual Insert Procs for 20-200 Inserts? https://stackoverflow.com/questions/8618261/sqlbulkcopy-vs-tvp-vs-xml-parameter-vs-individual-insert-procs-for-20-200-ins
3) Table-Valued Parameters https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

 

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google photo

您的留言將使用 Google 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s