sql - 我可以在 SQL Server 上创建动态表值函数吗？

Question

我正在尝试在 SQL 中编写一个函数，该函数应该返回一个表，该表是模式中所有表的并集。这可以在 SQL Server 中做到吗？

我经常需要在模式中连接表，为此我从这里找到了一个非常好的动态查询:

How to SELECT and UNION from a group of Tables in the schema in SQL Server 2008 R2

(来自 MarkD 的回答)

现在，因为我需要在不同的模式上重复执行此操作。我正在考虑编写一个函数，该函数将模式名称和表名称的通配符作为输入，并输出所有表的联合。

但是，我担心在构造 create 函数时可能需要声明表列 - 我不能这样做，因为表应该是动态的!

我对 SQL 函数并不完全熟悉，我希望我能像在 Python 中那样做到这一点。这可能吗？

CREATE FUNCTION udfJoinTablesinSchema
     (@Schemaname VARCHAR(100), 
      @TableNameWildcard VARCHAR(100))
RETURNS TABLE
AS
     RETURN 
        (DECLARE @SelectClause VARCHAR(100) = 'SELECT *',
                 @Query NVARCHAR(MAX)   = '' 

         SELECT @Query = @Query + @SelectClause + ' FROM ' + TABLE_SCHEMA+'.['+TABLE_NAME + '] UNION ALL '
         FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_NAME like @TableNameWildcard
          AND TABLE_SCHEMA = @Schemaname 

         SELECT @Query = LEFT(@Query, LEN(@Query) - LEN(' UNION ALL '))

         PRINT(@Query)
         EXEC(@Query)
        );

我收到一条错误消息:

Incorrect syntax near 'DECLARE'. Expecting '(', SELECT or WITH." when I hover over DECLARE.

它还提示两个标量变量 @TableNameWildCard , @Schemaname .

Answer 1

不

这是 SQL Server 中的一个明确限制。存储过程能够在 user-defined functions 时更改表数据不是。
虽然动态 SQL 不一定更改表数据，但它确实需要存储过程调用。由于存储过程允许对数据进行更改，因此不能在表值函数中使用它。
使用存储过程可以解决您的问题。