Building and returning a table object without prior knowledge of the schema.

I have an extended stored procedure I wrote back in the days of SQL 7 to handle metadata. While not important to the question, clients use the metadata to store enterprise configuration data and then join these results with warehouse data to produce reports, etc.

This xp returns a table with a different schema depending on the request.

Based on the capabilities of SQL 7 and SQL 2000 I was forced to create a temporary table ahead of time and use the INSERT {table} EXEC xp syntax to place the returned resultset into an object I could subsequently use in my joins.

I have looked at functions and at a very shallow level into the .net CLR options now available, but it doesn't seem as if they offer anything new in this area.

asked Dec 10 '09 at 05:37 PM in Default

webatxcent
11 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

You can use a .NET CLR Stored Procedure instead of the XP, but you would still need to get the results into a table to join onto them. Of course, that clr proc could create the table for you if you wanted. If you have a look at Phil Factor Challenge 3 on this site, my entry there is a CLR stored procedure. The source code is there, so you could do what you wanted based on your input parameters, return your table with whatever schema (or create it as a temp table).

But I have a feeling that's not really what you want...

answered Dec 10 '09 at 05:46 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

@Matt: No not really. Creating the stored proc within the CLR doesn't relieve me of the need to dump the data into a temp table. I can do the same thing--create the temp table--within the existing extended procedure construct, but what I found then was that even if I created the skeleton of the table in advance of calling the xp, the code after the xp call would not recognize the new fields that were added by the xp. :(. It would be great if SQL had a syntax that would allow you to create a virtual table reference which could be composed of an sp or fn. SELECT * FROM ( dbo.fnMyFunc() ) tbl

Dec 17 '09 at 06:59 PM webatxcent

(comments are locked)

I'm not sure where/how you are collecting the metadata, but it sounds like a table-valued function should work. If the data is coming from outside SQL Server, then a CLR function should do the trick. Read this for more information: http://msdn.microsoft.com/en-us/library/ms131103.aspx

Opening paragraph:

Differences Between Transact-SQL and CLR Table-Valued Functions

Transact-SQL table-valued functions materialize the results of calling the function into an intermediate table. Since they use an intermediate table, they can support constraints and unique indexes over the results. These features can be extremely useful when large results are returned.

In contrast, CLR table-valued functions represent a streaming alternative. There is no requirement that the entire set of results be materialized in a single table. The IEnumerable object returned by the managed function is directly called by the execution plan of the query that calls the table-valued function, and the results are consumed in an incremental manner. This streaming model ensures that results can be consumed immediately after the first row is available, instead of waiting for the entire table to be populated. It is also a better alternative if you have very large numbers of rows returned, because they do not have to be materialized in memory as a whole. For example, a managed table-valued function could be used to parse a text file and return each line as a row.

answered Dec 10 '09 at 06:49 PM

Tom Staab
5.8k ● 5 ● 8 ● 10

I did think of that initally, but with a CLR function, as with any SQL function, you have to define the schema of what will be returned. You can't return a result set of arbitrary shape like you can with a stored procedure...

Dec 10 '09 at 07:08 PM Matt Whitfield ♦♦

Excellent point, Matt. I hadn't thought of it quite that way. I missed a key word when I read the question before: This xp returns a table with a different SCHEMA depending on the request. That makes a big difference.

Dec 10 '09 at 10:32 PM Tom Staab

The metadata comes from a DB and app we have written atop SQL to allow clients to maintain organizational entities and attributes and relationships at a high level. For example, one of our clients is a retailer who uses it to maintain hierarchies of management, location, etc. The xp executes a SQL-like query syntax to return entities and attributes. For example if the client wanted all closed stores in PA they could write SELECT STORE.CODE, STORE.CLOSEDATE FROM STORE->LOCATION WHERE LOCATION$STATE = 'PA'. The fact that there is a STORE entity is the client's doing.

Dec 17 '09 at 06:48 PM webatxcent

(comments are locked)