With stored procedures, you can have return values in addition to input and output parameters. ADO+ treats a return value from an stored procedure just like an output value. The following stored procedure takes a customerID as an input parameter, returns the companyname as an output parameter, and has a return value.
CREATE PROCEDURE GetCompanyName @CustomerID nvarchar(5), @CompanyName nvarchar(40) output as Select @CompanyName = CompanyName from Customers where CustomerID = @CustomerIDGO Return 99
The example bellows shows the code to call the GetCompanyName stored procedure.
[VB]
Imports System Imports System.Data Imports System.Data.ADO Namespace ConsoleApplication1 Module Module1 Shared Sub Main() Console.WriteLine("Requires a CustomerID to be passed in as a argument") Console.WriteLine("example: OutParamsWithACommand ALFKI") ' Create a new Connection and DataSetCommand Dim myConnection As ADOConnection = New ADOConnection _ ("provider=SQLOLEDB;database=northwind;data source=delphi;user id=sa;") Dim myCommand As ADOCommand = New ADOCommand("GetCompanyName", myConnection) myCommand.CommandType = CommandType.StoredProcedure; Dim workParam As ADOParamater = Nothing workParam = myCommand.Parameters.Add("RETURN VALUE", ADODBType.Integer) workParam.Direction = ParameterDirection.ReturnValue workParam = myCommand.Parameters.Add("@CustomerID", ADODBType.Char, 5) workParam.Direction = ParameterDirection.Input workParam.Value = args(0) workParam = myCommand.Parameters.Add("@CompanyName", ADODBType.Char, 40) workParam.Direction = ParameterDirection.Output Try myConnection.Open() myCommand.Execute() Console.WriteLine("CompanyName= " & _ myCommand.Parameters("RETURN_VALUE").Value) Console.WriteLine("CompanyName= " & _ myCommand.Parameters("@CompanyName").Value) Catch e As Exception Console.WriteLine(e.ToString) Finally myConnection.Close End Sub End Module End Namespace
[C#]
using System; using System.Data; using System.Data.ADO; class ConsoleApplication { public static void Main(String[] args) { if (args.Length < 1 ) { Console.WriteLine("Requires a CustomerID to be passed in as a argument"); Console.WriteLine("example: OutParamsWithACommand ALFKI"); return 0; } // Create a new Connection and DataSetCommand ADOConnection myConnection = new ADOConnection("provider=SQLOLEDB;database=northwind;data source=delphi;user id=sa;"); ADOCommand myCommand = new ADOCommand("GetCompanyName", myConnection); myCommand.CommandType = CommandType.StoredProcedure; ADOParameter workParam = null; workParam = myCommand.Parameters.Add("RETURN_VALUE", ADODBType.Integer); workParam.Direction = ParameterDirection.ReturnValue; workParam = myCommand.Parameters.Add("@CustomerID", ADODBType.Char, 5); workParam.Direction = ParameterDirection.Input; workParam.Value = args[0]; workParam = myCommand.Parameters.Add("@CompanyName", ADODBType.Char, 40); workParam.Direction = ParameterDirection.Output; try { myConnection.Open(); myCommand.Execute(); Console.WriteLine("CompanyName= " + myCommand.Parameters["RETURN_VALUE"].Value); Console.WriteLine("CompanyName= " + myCommand.Parameters["@CompanyName"].Value); } catch(Exception e) { Console.WriteLine(e.ToString()); } finally { myConnection.Close(); } } }
The only requirement to get the return value from a stored procedure is to mark the direction of the first parameter (in the parameters collection) as output.