Is there a way to define a SQL Server stored procedure that returns a recordset

Question

We have an internal application that generates ASP code to invoke Oracle and SQL stored procedures.

This application requests the appropriate data dictionaries and can determine the information about the parameters and accordingly build the call. A developer using this application can include code in his project and transfer data to it using a dedicated DTO (also created by the application).

At Oracle, we can happily determine if a recordset is returned, since we use refcursors and they appear in the parameter list in Oracle DDL.

This does not apply to SQL Server. Currently, developers themselves must know whether SQL Server SP returns a recordset, and check the option on the interface. This, in turn, determines whether the code generates, contains ExecuteQueryor ExecuteNonQuery.

While this is normal, it would be nice to have this option. Is there a way in which it can be determined programmatically by checking the data dictionary or in other ways?

Thank,

James

Answer 1

You can check the result set format created by the SQL statement using SET FMTONLY [ON|OFF]. MS tools, such as Reporting Services, use this method to determine the structure of output result sets.

, . - - .

(, ), . .

Answer 2

, , , , . , , .

, , , , .

Answer 3

It may also help: metadata from stored procedures

Answer 4

Try the option: - select o.name, p.name, t.name, p.max_length, p.is_output from sys.parameters p internal connection sys.objects o on o.object_id = p.object_id internal connection sys.types t on t.user_type_id = p.user_type_id order by o.name;

(You can create this as a view).