0

Is anyone aware of a native way to attaching business meta-data to a SQL Server result-set? or does anyone know of formal standard(s) exist for doing so? This is a synopsis of the scenario being addressed:

A Stored procedure is called from a .NET module (MVC/LINQ), and returns a result-set addressing an end user query.

(while not directly relevant to my question, just for completeness sake -- the .NET module converts the response-set to JSON and ships the payload back to a web SPA (single page app)).

    EXEC sp_Orders @Customer='12345'

The result-set:

    Customer_ID Customer_Name, ... SalesPerson  Region Order_Num  Order_Date Order_Total
    ----------- -------------, ... -----------  ------ ---------  ---------- -----------
    12345       John Doe           Mark Smith   West   2011-02-01 ACD2212    1024.66
    12345       John Doe           Mark Smith   West   2011-04-08 ACD4301    31922.00
    12345       John Doe           Mark Smith   West   2011-07-06 ACD4301    3223.00

    ... 500 records

The first n columns of data are typically repeated multiple times, when I only need them once. Is there a way to attach those as some form of metadata to the response payload?

Important: This is not the same question as can I return two result-sets from a single stored procedure, I know that is possible per: Retrieve data from stored procedure which has multiple result sets. This is a question specifically about a SQL Server native approach (or some form of standard) to attaching METADATA to a result-set.

To preempt some inevitable suggestions/comments, please consider:

I'm aware...

  1. it is possible to perform a separate query to get the information.
  2. that we can store some or all of the information in the user session.
  3. that some of the information ahead of time (such as customer ID).
  4. we can pre-process the response in .NET.

--While these are all viable solutions/suggestions, one should consider that not all the information is known ahead of time (e.g. sales person, info, address, etc), there is a limit to how much of over 1,000 customer related meta-data related fields we'd like to store in a session, and our internal/external sites use a large number of such meta data snippets on a very frequent basis. It is our preference to make only one trip to the database. solution in question here is preferable to us, (if doable without performance impact, or major complexity).

Some countries being served still connect to the internet with modems, so the amount of data being shipped over the wire is also a core consideration.

Pre-processing in .NET is an approach we considered, but not our first preference if some native solution/standard exists.

What I'm looking for in a response...

  1. A native solution, or official standard (A CLR based solution, while natively supported in SQL server, is not in the cards at this time).

What I'm NOT looking for...

  1. a suggestions to optimize what/when procs are called, or what is stored in a session, etc. these do not address the question (side note, optimization, design patterns, etc are all, explored, implemented, and/or non-relevant territory).

  2. suggestion that this is a bad idea, without providing an alternative not already mentioned above. That's ok if all you want is to express an opinion ;), but does not address the question.

Any suggestions, arrows pointing in the best direction is appreciated.

Community
  • 1
  • 1
Yossi
  • 331
  • 1
  • 3
  • 12
  • 1
    By its nature, sql server will return a relation as a result set. I do not believe there is any way to add information to that, treating the dataset as a .net object. Perhaps you could return the result as xml, which would give the hierarchical form that you are seeking. – Mark Wojciechowicz Aug 12 '14 at 20:00
  • As Mark suggested, you could use a logical hierarchy. Since you are using JSON, take a look at [this answer](http://stackoverflow.com/a/19317823/772086). – Mike Aug 12 '14 at 23:17

0 Answers0