Is there any way of determining of a SQL Server Stored Procedure returns a recordset

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, det开发者_如何学JAVAermines 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

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.

No, because you can write a procedure that only returns result sets sometimes, based on whatever logic or parameters you choose. It would most likely be hard to maintain, but there is nothing preventing you from doing it.

Therefore, in the general case you can never know if this execution of a procedure will return a result set or not, and that means there's no metadata the system could usefully store about it.

This may also help: metadata from stored procedures

Try a variation on :- select o.name, p.name, t.name, p.max_length, p.is_output from sys.parameters p inner join sys.objects o on o.object_id = p.object_id inner join sys.types t on t.user_type_id = p.user_type_id order by o.name;

(You could create this as a view).