CODEWITHSUNDEEP
sql-server
naz
naz

Reputation: 2072

Table valued functions, dynamic column names

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?

Upvotes: 2

Views: 3841

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48786

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

Upvotes: 2

SharK
SharK

Reputation: 2215

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

Upvotes: 2

user2361044
user2361044

Reputation: 26

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

Upvotes: 1

Related Questions