Can I create a dynamic table-valued function on SQL Server?

Question

I'm trying to write a function in SQL that should return a table which is the union of all tables within a schema. Is this possible to do in SQL Server?

I frequently need to concatenate tables within a schema, and for that I found a really nice dynamic query from here:

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

(The answer from MarkD)

Now, since I need to do this repeatedly, over different schemas. I was thinking of writing a function that would take as input the schema name and wildcard for the table name, and output a union of all the tables.

However, I fear that I might need to declare the table columns when constructing the create function - which I cannot do, because the table is supposed to be dynamic!

I'm not entirely familiar with SQL functions, and I'm hoping I could do this like I would in maybe Python. Would this be possible?

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

I get an error message:

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

Also it complains about the two scalar variables @TableNameWildCard, @Schemaname.

You can't use dynamic SQL inside a user-defined function in SQL Server. — Zohar Peled
– Zohar Peled, Commented Jul 17, 2019 at 15:43
What @ZoharPeled said. But you could do it with a stored procedure. — Eric Brandt
– Eric Brandt, Commented Jul 17, 2019 at 15:45
instead of '...['+TABLE_NAME + ']...' you should be using '...' + QUOTENAME(TABLE_NAME) + '...'. Just like a string used to inject into a WHERE, a string used to inject for an object can suffer injection attacks. — Thom A
– Thom A ♦, Commented Jul 17, 2019 at 15:45
If you have tables with the same structure all over the place I would say that finding a query is not the problem. Multiple identical tables is a very clear sign that the design is the real issue here. This kind of thing is almost always better using one additional column to identity the "type" instead of dozens of identical tables. — Sean Lange
– Sean Lange, Commented Jul 17, 2019 at 15:45
Also, the reason for the error is because you have declared the function as an inline table-value function, but written it as a mult-line table-value function. The latter are really poor performers, however, even if you could use dynamic SQL in a function, it would be impossible to write a tvf as an inline one for this. Fortunate you can't, i suppose. — Thom A
– Thom A ♦, Commented Jul 17, 2019 at 15:47

Paul · Accepted Answer · 2020-10-22 15:44:15Z

8

No

This is an explicit limitation in SQL Server. Stored procedures are able to change the table data while user-defined functions are not.

While dynamic SQL does not necessarily change the table data, it does require a stored procedure call. Since a stored procedure would allow for changes to the data, it cannot be used from within a table-valued function.

Using a stored procedure is a possible solution to your problem.

edited Oct 22, 2020 at 15:44

answered Aug 5, 2020 at 17:20

Paul

3,9641 gold badge20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can I create a dynamic table-valued function on SQL Server?

1 Answer 1

No

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

No

Comments

Linked

Related