3

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.

Tom Schreck
  • 5,177
  • 12
  • 68
  • 122

1 Answers1

4

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • thanks for the feedback. Each resultset in stored proc is associated to an interface so there is no variance. The benefit of using SET FMTONLY is you do not have to provide any parameters. You can pass null instead. With your solution, would you not have to supply parameters in order to run the stored procs? – Tom Schreck Aug 19 '14 at 03:26
  • Yes, you'll need to pass all mandatory parameters. I just pass NULL, just like with SET FMTONLY ON, after retrieving the parameter meta-data (needed for the interface contract anyway). – Dan Guzman Aug 19 '14 at 03:36
  • I was thinking, could the danger of invoking the procedure with dummy data be worked-around by invoking it within a `TRANSACTION` which is always rolled-back afterwards? I forget - if a procedure performs a `COMMIT` within a parent `TRANSACTION` which is `ROLLBACK`, what happens? – Dai Jan 11 '21 at 11:04