有没有办法确定SQL Server存储过程返回记录集(Is there any way of determining of a SQL Server Stored Procedure returns

有没有办法确定SQL Server存储过程返回记录集(Is there any way of determining of a SQL Server Stored Procedure returns a recordset)

我们有一个内部应用程序，它生成ASP代码来调用Oracle和SQL存储过程。

该应用程序查询相应的数据字典，并能够确定参数信息，并相应地构建调用。 使用此应用程序的开发人员可以在其项目中包含代码，并使用专用DTO（也由应用程序生成）将数据传递给它。

在Oracle中，我们可以愉快地确定是否返回记录集，因为我们使用了refcursors，并且这些记录显示在Oracle DDL的参数列表中。

对于SQL Server，情况并非如此。 目前，开发人员必须知道SQL Server SP是否返回记录集，并在界面上勾选一个选项。 反过来，这将确定代码生成是否包含ExecuteQuery或ExecuteNonQuery 。

虽然这没关系，但是选择这个选项会很好。 有没有办法通过检查数据字典或其他方式以编程方式确定？

谢谢，

詹姆士

We have an internal application which generates ASP code to call Oracle and SQL Stored procedures.

This application queries the respective data dictionaries, and is able to determine parameter information, and construct the call accordingly. A developer using this application can include the code in their project, and pass data to it using a dedicated DTO (also generated by the app).

In Oracle, we can happily determine if a recordset is returned, as we use refcursors, and these show up in the parameter list on the Oracle DDL.

This not the case for for SQL Server. Currently the developers themselves have to know whether the SQL Server SP returns a recordset, and tick an option on the interface. This, in turn, determines whether the code generates contains ExecuteQuery or ExecuteNonQuery.

While this is ok, it would be nice no to have that option. Is there a way that his can be determined programatically by inspecting the data dictionary or by some other means?

Thanks,

James

最满意答案

您可以使用SET FMTONLY [ON|OFF]检查SQL语句生成的结果集格式。 Reporting Services等MS工具使用此技术来确定输出结果集的结构。

这对于存储过程来说很棘手，因为输出结果集的数量和结构可能会根据输入参数值而有所不同。 如果您的程序代码非常重要，那么即使使用严格的编码标准，也很难解决这个问题。

我见过其他方法（例如使用扩展参数），但没有一种方法是绝对可靠的。 让开发人员勾选方框可能是最好的解决方案。

You can inspect the resultset format produced by a SQL statement using SET FMTONLY [ON|OFF]. MS tools such as Reporting Services use this technique to determine the structure of output resultset(s).

This is tricky for stored procedures because the number and structure of the output result sets could vary depending on the input parameter values. This is difficult to get around - even with strict coding standards - if your procedure code is non-trivial.

I've seen other approaches (such as using extended parameters), but none are infallible. Having the developers tick a box may be the best solution.