How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).