Stored Procedure Output Parameters to Retrieve a Single Row

Stored Procedure Output Parameters to Retrieve a Single Row
You can call a stored procedure that returns the retrieved data items within a single row by means of named output parameters. The following code fragment uses a stored procedure to retrieve the product name and unit price for a specific product contained in the Products table in the Northwind database.

void GetProductDetails( int ProductID,

 out string ProductName, out decimal UnitPrice )

{

  using( SqlConnection conn = new SqlConnection(

        "server=(local);Integrated Security=SSPI;database=Northwind") )

  {

    // Set up the command object used to execute the stored proc

    SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn )

    cmd.CommandType = CommandType.StoredProcedure;

    // Establish stored proc parameters.

    //  @ProductID int INPUT

    //  @ProductName nvarchar(40) OUTPUT

    //  @UnitPrice money OUTPUT

    // Must explicitly set the direction of output parameters

    SqlParameter paramProdID =

             cmd.Parameters.Add( "@ProductID", ProductID );

    paramProdID.Direction = ParameterDirection.Input;

    SqlParameter paramProdName =

             cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );

    paramProdName.Direction = ParameterDirection.Output;

    SqlParameter paramUnitPrice =

             cmd.Parameters.Add( "@UnitPrice", SqlDbType.money );

    paramUnitPrice.Direction = ParameterDirection.Output;

    conn.Open();

    // Use ExecuteNonQuery to run the command.

    // Although no rows are returned any mapped output parameters

    // (and potentially return values) are populated

    cmd.ExecuteNonQuery( );

    // Return output parameters from stored proc

    ProductName = paramProdName.Value.ToString();

    UnitPrice = (decimal)paramUnitPrice.Value;

  }

}