Table Valued Function with dynamic select statement

Question

Creating a user-defined table-valued function which should return select union all dynamic query.

I have table tbl_tablesinfo which contains table names tbl1, tbl2, tbl3, etc. in all around 3000 table names.

I don't want to create view but function which should return select * from all tables by doing union all.

My attempt:

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

I'm getting an error:

Incorrect syntax near 'BEGIN'.

Reason for doing this is creating view with 3k tables is getting slow and taking around 30 min of time, so I am looking for an alternative by creating function.

By saying returns table without defining the table you are creating an inline table valued function which can only have a return select in it. It you want a regular table valued function you need to define the return table. Please consult the docs. — Dale K
– Dale K, Commented Nov 5, 2019 at 5:52

gotqn · Accepted Answer · 2019-11-05 06:16:06Z

In the docs is clear said, that:

User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

which means that you need to use a stored procedure and this is not bad as you can still insert the data in table if you want:

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

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

So, in your case you will have:

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

Note, actually you can execute dynamic T-SQL in function but this is special case using SQL CLR. I can show you how to do this, but it will be better to stuck with the stored procedure.

morgan121 · Accepted Answer · 2019-11-05 08:01:00Z

0

You can't use Dynamic Query in SQL Function...
Functions can return only Scalar Values, or Tables...
Instead you can use Stored Procedure...

edited Nov 5, 2019 at 8:01

morgan121

2,2531 gold badge19 silver badges38 bronze badges

answered Nov 5, 2019 at 6:24

Akhileshkumar singh

1,5321 gold badge10 silver badges21 bronze badges

1 Comment

Zohar Peled Over a year ago

While this is technically correct it's not much of a quality answer... this would be better off as a comment.

Collectives™ on Stack Overflow

Table Valued Function with dynamic select statement

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related