时间戳

首页 > 解决方案 > 具有动态选择语句的表值函数

sql - 具有动态选择语句的表值函数

问题描述

创建应返回select union all动态查询的用户定义的表值函数。

我有一个表tbl_tablesinfo，其中包含大约 3000 个表名中的表名tbl1、、tbl2等tbl3。

我不想创建视图，而是创建应该select * from通过执行返回所有表的函数union all。

我的尝试：

CREATE FUNCTION udf_alldata()
RETURNS TABLE
AS
BEGIN
    DECLARE @Var VARCHAR(MAX) = ''

    SELECT 
        @Var = STUFF((SELECT ' SELECT * FROM [' + tbl.TableNames + '] UNION ALL'
                      FROM [TestDB].SYS.TABLES tb 
                      INNER JOIN [TestDB].dbo.[tbl_tablesinfo] tbl ON tb.name = tbl.TableNames
                      FOR XML PATH('')), 1, 1, '');

    SET @var = LEFT(@var, LEN(@var) - 10);

    EXEC @var 

    RETURN
END

我收到一个错误：

'BEGIN' 附近的语法不正确。

这样做的原因是使用 3k 表创建视图变得越来越慢并且需要大约 30 分钟的时间，所以我正在通过创建函数来寻找替代方案。

标签： sqlsql-servertsqlsql-server-2012

解决方案

在文档中明确表示：

用户定义的函数不能使用动态 SQL 或临时表。允许使用表变量。

这意味着您需要使用存储过程，这还不错，因为您仍然可以根据需要在表中插入数据：

INSERT INTO @Table
EXEC [dbo].[stored_procedured_name]

INSERT INTO #Table
EXEC [dbo].[stored_procedured_name]

因此，在您的情况下，您将拥有：

CREATE PROCEDURE udf_alldata
AS
BEGIN
    DECLARE @Var VARCHAR(MAX) = ''

    SELECT 
        @Var = STUFF((SELECT ' SELECT * FROM [' + tbl.TableNames + '] UNION ALL'
                      FROM [TestDB].SYS.TABLES tb 
                      INNER JOIN [TestDB].dbo.[tbl_tablesinfo] tbl ON tb.name = tbl.TableNames
                      FOR XML PATH('')), 1, 1, '');

    SET @var = LEFT(@var, LEN(@var) - 10);

    EXEC sp_executesql @var 

    RETURN
END

请注意，实际上您可以执行dynamic T-SQL in function，但这是使用 SQL CLR 的特殊情况。我可以向您展示如何做到这一点，但最好坚持使用存储过程。

推荐阅读