NGWS SDK Documentation  

This is preliminary documentation and subject to change.
To comment on this topic, please send us email at ngwssdk@microsoft.com. Thanks!

Input / OutPut Paramaters and Return Values

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.