CODEWITHSUNDEEP
sqlsql-serverfunctiont-sqldynamic
Akshay
Akshay

Reputation: 189

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

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.

Upvotes: 6

Views: 10948

Answers (1)

Paul
Paul

Reputation: 3884

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.

Upvotes: 8

Related Questions