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

2024-05-06 • 问答

创建一个用户定义的表值函数，该函数应返回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,'');

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

    EXEC @var 

    RETURN
END

我遇到错误：

“ BEGIN”附近的语法不正确。

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

guyueningxiang 回答：具有动态选择语句的表值函数

在docs中明确说：

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

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

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,'');

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

    EXEC sp_executesql @var 

    RETURN
END

因此，根据您的情况，您将拥有：

dynamic T-SQL in function

注意，实际上您可以执行{{1}}，但这是使用SQL CLR的特殊情况。我可以向您展示如何执行此操作，但是最好使用存储过程。

您不能在SQL函数中使用动态查询...
函数只能返回标量值或表...
相反，您可以使用存储过程...

sql sql-server sql-server-2012 tsql

本文链接：https://www.f2er.com/3162077.html

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

guyueningxiang 回答：具有动态选择语句的表值函数

大家都在问