Attach extended properties to a stored procedure result set

sql-server, sql-server-2008, sqlclr

Solution

Why not return 2 result sets from the SP? One is the actual result set, the one you have now and the other one is the metadata.

The metadata table can be a table variable created in the SP, with this structure:

DECLARE @ResultsMetadata AS TABLE
(
  Id INT NOT NULL IDENTITY(1,1),
  ColumnName VARCHAR(128) NOT NULL,
  ColumnMetadata VARCHAR(128) NOT NULL
)

The you can read this in what I assume is a CLR (because of the tag). It's easy to read multiple result sets with an `SqlDataReader`. If you need to process the results further also in SQL, then perhaps you can switch to an XML output with two top level elements (main result set and meta data).

EDIT

Actually just noticed that you're reading this with ADO.NET, so you shouldn't have any problems with multiple result sets. You can use `SqlDataReader.NextResult` to advance the reader to the second (metadata) result set.

Problem

I'm trying to attach metadata to the result set of a stored procedure. The procedure would return a table, either as the result of a SELECT query, or a temporary table built in the procedure itself. I'd like to decorate its columns with additional information, to sort of emulate .NET's attributes. Then, when executing the procedure with `ADO.NET`, I want to evaluate this metadata. As far as I can tell, this can't easily be done. I could perhaps work around it by creating a global temporary table (`##` prefix), then manually attaching extended properties to it in `tempdb`. Any ideas?

Original source