Function that returns a dynamically defined table

Question

Function that returns a dynamically defined table

G33kKahuna

SSCommitted

Points: 1726
More actions
June 6, 2008 at 7:44 am

#183182

Currently I have a stored procedure that accepts a list of columns as the input and upon execute, selects these columns dynamically from tables. This is just the 100K foot view of the sp.
I need to join the output with other tables. One option is to the exec results in a table variable or temp table and perform the join. Not that elegant.
I was hoping I could make this as a function that returns a table with dynamically defined columns.
any help is much appreciated.
thanks

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

A T-SQL function probably will not be appropriate for this. At this point I can't think of a way to even "cheat" to make a T-SQL function do that, since using EXEC and sp_ExecuteSQL aren't going to be allowed (you can only executed extended stored procs from within a function, so EXEC+dynamic SQL isn't allowed, and you cannot invoke "regular" stored procs, so sp_ExecuteSQL is out).

CLR will allow you to do dynamic SQL, although I don't know if you have any control over picking columns. Meaning - you can definitively change HOW the columns are filled, I am just not really sure how you would go about changing WHICH columns you might need.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 2

Another guy just asked for something similar over in the Development forum. I had the same answer for him. I kind of thought it might be possible in CLR. Is that not true?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Grant Fritchey (6/6/2008)
Another guy just asked for something similar over in the Development forum. I had the same answer for him. I kind of thought it might be possible in CLR. Is that not true?

Part of the function definition in CLR seems to require that you define the column names (two places: the TableDefinition attribute in the SqlFunction declaration, and in the FillRowMethod).

Since you don't have any play on how the fillRowMethod is called - I don't see any way to overload it, nor have I found a way around hard-coding all columns into the TableDefinition. I've even tried to set up a FillRowMethod returning only some of the defined columns or different names, and you can't even get past the parser phase (meaning - it won't build).

Still - you could build dummy columns and not fill them in all cases I suppose, but still - we're not talking free reign on picking what columns to return on the fly. combining it with Jeff's tricks about passing arrays back might work (put some dynamic columns into a big column to be parsed afterwards), but by then - you might as well be building the temp table.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 4

OK. That makes sense. I'm also not surprised it works like that. Thanks for the input.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

noeld SSC Guru Points: 96590 More actions · Answer 5

Kaushik Kumar (6/6/2008)
Currently I have a stored procedure that accepts a list of columns as the input and upon execute, selects these columns dynamically from tables. This is just the 100K foot view of the sp.
I need to join the output with other tables. One option is to the exec results in a table variable or temp table and perform the join. Not that elegant.
I was hoping I could make this as a function that returns a table with dynamically defined columns.
any help is much appreciated.
thanks

There is a trick but is not as elegant as you may like. I think using a Temp table is the way to go!

* Noel

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

Even with a temp table, you are going to have to dynamically generate the create statement for the temp table and then execute the procedure inserting the records into it. This will mean doing pretty much the entire operation in dynamic SQL.

This is going to get pretty ugly very quickly. You may want to go back and re-think the approach. Stored procedures are not well suited for ad-hoc querying when the column definitions of the returned data is going to constantly be changing. You may want to try to come up with an approach in which you can better anticipate the columns you need to return or even move from stored procedures to a completely ad-hoc querying approach where you can use an application layer to more easily handle something like this.