Table valued functions, dynamic column names

Question

Is there a way to name the column in a table valued function depending on the input of the user to the function?

Suppose there is some function:

foo(1,2,3)

So it takes three integer inputs. Now suppose I would like to return the column names in the table like: '1 some text', '2 some text', '3 some text'.

Is this possible?

Have you tried appending the function arguments as an alias names in the result set!? — SharK
– SharK, Commented Jul 7, 2016 at 9:51
what is the syntax for that? this does not seem to work: INSERT INTO \@Table SELECT \@parameter1 as (\@parameter1 + 'some text') — naz
– naz, Commented Jul 7, 2016 at 10:01
In order to create the column names for the result set, need to use EXEC inside the function which the UDFs doesn't support. However this can be achieved using the procedures. I have mock-up for a procedural retrieval. Let me know if that helps, — SharK
– SharK, Commented Jul 7, 2016 at 10:54

SharK · Accepted Answer · 2016-07-07 11:07:42Z

2

If you need an SP approach, you can extend it using,

CREATE PROCEDURE Foo(@Input1 INT, @Input2 INT, @Input3 INT) AS 
DECLARE @X VARCHAR(100) 

SET @X = 'Select ' + 'Id ' + ' AS [' + convert(varchar(10), @Input1) + 'SomeText]' + 
', FirstName ' + ' AS [' + convert(varchar(10), @Input2) + 'SomeText' + ']' + 
', LastName ' + ' AS [' + convert(varchar(10), @Input3) + 'SomeText' + ']' +
         ' From Emp' 

EXEC (@X) 

GO 

Foo 1, 2, 3

answered Jul 7, 2016 at 11:07

SharK

2,2231 gold badge23 silver badges28 bronze badges

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

Comments

Solomon Rutzky · Accepted Answer · 2016-07-09 17:34:28Z

No. Table-Valued Functions -- Multi-statement TVF, Inline TVF, or SQLCLR TVF -- need to return a consistent / deterministic result set. This is similar to Scalar UDFs (T-SQL and SQLCLR) needing to always return the same datatype.

In fact, the result set to be returned by any TVF, regardless of the type of TVF, is defined in the system meta-data when the TVF is created, and cannot be changed at run-time. You can see the definitions using the following query:

SELECT so.type_desc AS [ObjectType], so.[name] AS [ObjectName], sc.*
FROM sys.columns sc
INNER JOIN sys.objects so
        ON so.[object_id] = sc.[object_id]
WHERE so.type_desc NOT IN ('SYSTEM_TABLE', 'USER_TABLE', 'INTERNAL_TABLE',
                           'VIEW', 'TYPE_TABLE')
ORDER BY so.[type_desc], so.[name], sc.column_id;

For ObjectType, you can get back entries having the following values:

CLR_TABLE_VALUED_FUNCTION
SQL_INLINE_TABLE_VALUED_FUNCTION
SQL_TABLE_VALUED_FUNCTION

user2361044 · Accepted Answer · 2016-07-07 10:57:39Z

1

This can be achieved by using dynamic SQL, but remember you cannot have it inside a function. Try creating a SP..

answered Jul 7, 2016 at 10:57

user2361044

262 bronze badges

Collectives™ on Stack Overflow

Table valued functions, dynamic column names

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related