This project has moved and is read-only. For the latest updates, please go here.

Get schema for the result set returned by stored procedure

Jul 28, 2014 at 4:05 PM
Hi!

Is there a way to use your API to find out the schema of the resultset returned by the stored procedure?

I can see an empty ResultSets collection in the stored procedure definition.
Jul 28, 2014 at 9:03 PM
Result sets from stored procedures don't have schemas.
Procedures are a way of hiding all the details of schemas, tables and security. The underlying details of the internal query are not available.
Jul 29, 2014 at 9:20 AM
Hi!

Thanks for your prompt reply. Could you please advise on the ResultSets collection. What is it used for?
Jul 29, 2014 at 11:47 AM
Say you have a stored procedure like this (Oracle HR):
CREATE OR REPLACE PROCEDURE GET_ALL_COUNTRIES(
poc_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN poc_cursor FOR SELECT COUNTRY_ID , COUNTRY_NAME , REGION_ID FROM COUNTRIES;
END GET_ALL_COUNTRIES;

or this (Sql Server Northwind):
ALTER procedure [dbo].[Ten Most Expensive Products] AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

Both of them return a single result set, which is the result of the SELECT statements.
The ResultSet is made up of Columns, each which has a datatype and a name.
For instance, the Northwind one has a ResultSet with 2 columns, the first being "TenMostExpensiveProducts" of type nvarchar(40), the second being "UnitPrice" of type money.

Finding out what a stored procedure returns isn't easy without actually reading and understanding the source. The DatabaseSchemaReader has an option to read the result sets from the stored procedure. This is done like this:
var sprocRunner = new DatabaseSchemaReader.Procedures.ResultSetReader(databaseSchema);
sprocRunner.Execute(); //populates storedProcedure.ResultSets

It isn't done automatically by databaseReader.ReadAll() because it can be quite slow.

HTH
Jul 29, 2014 at 12:59 PM
Thanks! That is exactly what i have been searching for.