3

Is it possible to have a table-valued function in T-SQL return a table with a variable number of columns?
The column names may simply be 1, 2, …, n.

Right now I have a "string split" function that returns a single-columned 1 x n table, and I pivot the table afterwards to an n x 1 table, but I'd rather streamline the process by returning the correct table format to begin with.

I intend to use a CLR procedure in C# for this function, I just don't know how to set up the user-defined function to return my data in the format I want: with a variable number of columns, dependent on the input string.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Protector one
  • 6,926
  • 5
  • 62
  • 86
  • The feature is called **[Polymorphic Table Function](https://stackoverflow.com/a/49015504/5070879)** (defined in SQL Standard 2016) - not supported by T-SQL – Lukasz Szozda Dec 18 '20 at 11:09

1 Answers1

0

It is not possible to return a non-static Result Set from a Table-Valued Function (TVF), whether it be written in T-SQL or .NET / SQLCLR. Only Stored Procedures can dynamically create a Result Set.

Basically, any function needs to return a consistent result type, whether it is a scalar value or a collection (i.e. Result Set).

However, in a SQLCLR stored procedure, you can create a dynamic Result Set via SqlMetaData. As long as you don't have an explicit need to SELECT ... FROM it, then maybe a stored procedure would work.

Of course, you might also be able to get away with doing this in T-SQL, using dynamic SQL to construct a SELECT statement based on the output of your split function.

A lot of this comes down to the exact context in which this functionality needs to be used.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • I know about using SqlMetaData to create a result set in a CLR stored procedure, but is this also possible for a user-defined function? – Protector one Apr 12 '11 at 10:45
  • 1
    No, SqlMetaData is only for Stored Procedures. As mentioned in my answer, a function, regardless of the language, must return a consistent/predefined type. For a CLR function you define a struct to pass the data back in as well as the table definition in the function decorator. There is no way to make either of those places dynamic. I would LOVE it if you could make a function Result Set dynamic, but that just isn't going to happen. – Solomon Rutzky Apr 12 '11 at 12:41