T-SQL——关于表类型

目录

• 0. 说明
• 1. 最简示例
• 2. 使用场景
• 3. 关于C#中调用表值参数的存储过程
• 4.参考

0. 说明

• SQL Server 2008中引入了对表类型的支持。通过创建表类型，可以把表的定义保存到数据库中，以后在定义表变量、存储过程和用户定义的输入参数时，可以将表类型作为表的定义而重用。

1. 最简示例

--判断自定义表是否存在
IF TYPE_ID('CustomerTableType') IS NOT NULL
--删除用户自定义的表类型
EXEC SP_DROPTYPE CustomerTableType


----创建表类型
CREATE TYPE dbo.CustomerTableType AS TABLE(Id INT NOT NULL, Name NVARCHAR(10) NULL, Age INT NULL);

----使用表类型创建一个表变量
DECLARE @myTable CustomerTableType;
INSERT INTO @myTable(Id, Name, Age)VALUES(1, 'Tom', 30);

SELECT * FROM @myTable;

2. 使用场景

如何将一个表值作为存储过程的参数？

在2008之前如果我们想要将表作为输入参数传递给SQL Server存储过程使比较困难的，可能需要很多的逻辑处理将这些表数据作为字符串或者XML传入。
在2008中提供了表值参数。使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据，这样可以省去很多自定义的代码。这样的操作对于存储过程内基于表函数的操作变得非常容易操作。
注意，表值参数是使用用户定义的表类型来声明的。所以使用之前要先定义表类型。

常见的场景：

• 参考：https://blog.csdn.net/yenange/article/details/121261115

--判断自定义表是否存在
IF TYPE_ID('CustomerTableType') IS  NULL
----创建表类型
CREATE TYPE dbo.CustomerTableType AS TABLE(Id INT NOT NULL, Name NVARCHAR(10) NULL, Age INT NULL);


--创建一个存储过程，使用自定义表类型作为参数类型
----注意：表值参数必须作为 READONLY 参数传递到存储过程
CREATE PROC proc_TestTableType
      @c CustomerTableType READONLY
AS BEGIN
     --业务逻辑
END;

3. 关于C#中调用表值参数的存储过程

 using (SqlCommand cmd = new SqlCommand("存储过程名", conn))
 {
     cmd.CommandType = CommandType.StoredProcedure;
    
     SqlParameter parameter = new SqlParameter("@ProcParam", SqlDbType.Structured); //注意参数类型是：SqlDbType.Structured
    
     parameter.TypeName = "dbo.CustomerTableType"; //必须指定自定义的表类型名
    
     parameter.Value = dataTable; //赋值,参数就是C#中的Datatable对象

     cmd.Parameters.Add(parameter);
     cmd.ExecuteNonQuery();
 }

4.参考

• SQL Server 2008 中的表值参数 (ADO.NET)

• SqlServer: 存储过程传表类型参数的三种方式：表值参数/XML/JSON

• 表值参数简介及与 C# 的交互