sql - 如何从 SQL Server 中的现有表创建类型？

Question

我的数据库中已有许多表。我需要编写存储过程来获取与其中一些表相同的数据并在存储过程中处理它。

我知道可以根据类型创建表格。然而，有时为了简化单个存储过程而添加新类型是很麻烦的。

有什么方法可以根据现有表定义内联类型吗？

有什么方法可以根据现有表将其定义为存储过程参数？

Answer 1

Is there any way to define type inline based on existing table?

该功能称为: anchored data type 并且在 T-SQL 中没有直接的等效项。

An anchored type defines a data type based on another SQL object such as a column, global variable, SQL variable, SQL parameter, or the row of a table or view.

A data type defined using an anchored type definition maintains a dependency on the object to which it is anchored. Any change in the data type of the anchor object will impact the anchored data type. If anchored to the row of a table or view, the anchored data type is ROW with the fields defined by the columns of the anchor table or anchor view.

This data type is useful when declaring variables in cases where you require that the variable have the same data type as another object

IBM DB2 和 Oracle 数据库支持它。

---

备选方案:

将参数作为 XML/JSON 而不是表值参数传递。它将减轻程序维护类型的负担。

CREATE PROCEDURE my_proc @param XML -- instead of table type
AS
BEGIN
    -- here @param validation could be performed if needed

    SELECT s.c.value('col_name1', type_name) AS col_name1
          ,s.c.value('col_name2', type_name) AS col_name2
    INTO #tmp_param
    FROM @param p 
    CROSS APPLY p.nodes('...') s(c)

    SELECT *
    FROM #tmp_param;  -- any other use like normal table valued parameter
END

并调用它:

--Standard way
DECLARE @param table_type;
INSERT INTO @param(col1, col2) ...;
EXEC my_proc @param;

-- different way
DECLARE @param XML = (SELECT ... FROM ... FOR XML AUTO);
EXEC my_proc @param;

这里唯一的缺点是没有参数模式验证，它必须在存储过程中执行。