I understand after SQL Server 2012 SET FMTONLY will be deprecated. I've developed a sophisticated code generation system that analyzes the results of a stored procedure and generates C# code for repository, data contracts, and business library. I'm utilizing SET FMTONLY to analyze the resultset(s) of stored procedures to understand the datacontract the stored proc is producing. This contract gets generated into an interface and it's implementation. I'm able to take a stored procedure returning multiple recordsets and return the data as a data contract to client.
I cannot find a replacement for SET FMTONLY that analyzes all of the resultsets for a Stored proc. Does anyone have any insight on how I can retrieve the columns, data types, etc for each and every resultset for a stored proc?
Thanks for your help.
SET FMTONLY ON is flawed in that it considers all SELECT statements as unconditional. The replacements, sp_describe_first_result_set and sp_describe_first_result_set_for_object, only return meta data for the first result set so that will not work in your situation. Those procs also raise an error in the case of multiple results with incompatible meta data.
I suggest you retrieve the meta data in your C# code by actually executing the procs against a newly built and deployed database, using SqlDataReader methods to retrieve meta-data. This method also handles dynamic SQL well. The only case where this approach is problematic is if the data contract varies depending on the values passed, which is a design issue anyway.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments