User Defined Function Returning a Table

Question

User Defined Function Returning a Table

jnelson

SSC Journeyman

Points: 80
More actions
March 29, 2004 at 12:31 pm

#107869

I would like to create a user-defined function that returns data as a table -- the catch is that the structure of the returned table is not known in advance. I would like to build the structure on-the-fly based upon a parameter passed to the function (the parm is a virtual table name).
This way, the table returned by a call to dbo.VirtualTable('VTableId1') could be very different from the results returned from dbo.VirtualTable('VTableId2').
The examples from BOL illustrate only a table that has a set structure (column names, types, etc.). Do I need to explore another method?
Thanks, Jon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

vishy Mr or Mrs. 500 Points: 596 More actions · Answer 1

all you need to do is use dynamic sql to build you function and create it. You can prebuilt all you basic definitions and plug in the column names and types based on your input.

-vishy

jnelson SSC Journeyman Points: 80 More actions · Answer 2

I do not have a finite set of table definitions to set up in advance. Basically this function is querying a set of tables that both define the table and the data inside of it. But you mentioned creating the function dynamically, and I'm thinking on that one. Did you have in mind a wrapper function that creates a "subordinate" function and subsequently calls it, passing the returned table up the chain to the original caller? Or am I going somewhere else?

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

Its not going to be possible. If you need to return a table from a function you must be using it in a query where you must now the structure of the table otherwise how do you select the columns from it.

If you are just returning the data to a recordset the use dynamic sql in an SP and execute it.

i.e.

declare @sql varchar(100)

set @sql = 'select col1, col2 from mytable'

execute (@sql)

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Anilshetty Newbie Points: 5 More actions · Answer 4

you can try this using dynamci sql like this.

First create a temp table with a dummy column name and later on add columns dynamically on the fly.

see sample code blelow like this

SET @strSqlStmt = 'ALTER TABLE ' + @TempTableName + ' ADD [' + @strUtilityName + '] INT'

EXECUTE(@strSqlStmt)

jnelson SSC Journeyman Points: 80 More actions · Answer 5

Thanks Anilshetty, I think the temp table will be the way to go on this one.

Simon, there are special circumstances surrounding this one. They will never have a need to select only some columns, and logically no table would have more than 40 columns and average a lot less than that. The end users who define these tables use them in our ERP system, and just have a need to drop them into Excel on occasion for analysis purposes. Oddly enough, our ERP docs refer to these as 2-D tables as if that is somehow different than most other tables.