When a Teradata Stored Procedure is imported into the model, the Visual Studio Entity Data Model Wizard does not persist some facets of a function parameter such as MaxLength, Precision, Scale for the function imported to the SSDL file. This may have an impact in the execution of the function and its results. The following sections describe the changes that are required for an Input and Input Output parameter. Output parameters do not require any change to the SSDL file. Also we recommend using separate Input and Output parameter in-place of an Input Output parameter wherever possible.

A Facet provides additional Data Type information. For example

1. MaxLength facet can describe the maximum number of characters for a VarChar Stored Procedure parameter.
2. Precision facet can describe the maximum number of digits for a Decimal Stored Procedure parameter.
3. Scale facet can describe the maximum number sub-second digits for a TimeStamp Stored Procedure parameter.

Input Function Parameters

Whenever possible the Entity Provider will infer the Facet value from the Data or it will assign a compatible Facet value to the Function Parameter. The table below describes the inferred or assigned facet values for Input parameters of affected Data Types:

Input Output Function Parameters

The ObjectParameter that is created for specifying an Input Output parameter can be of .NET Data Type or Provider specific type (if applicable for the datatype). Please note that when a Provider Specific type is used to declare an Input Output parameter, the user can pass a Provider Specific value as Input but the output object will be of .NET Data type only. The table below describes the issues that might be seen with Input Output parameters and how they can be resolved with .NET Data type or Provider Specific type.

The SSDL must be manually modified to include correct MaxLength, Precision, and Scale facets values for a Function parameter where relevant as described above. For Input parameters, we recommend changes to Time, Timestamp, Timestamp With Time Zone, BLOB and CLOB data types (see table for Input parameters). Also whenever possible, we recommend using separate Input and Output parameter instead of using a single Input Output parameter.

Here is an example of a generated and modified Function element in the SSDL.

<Function Name="BranchInsert" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false"
          ParameterTypeSemantics="AllowImplicitConversion" Schema="CarRentalModel">
    <Parameter Name="Branchid" Type="integer" Mode="In" />
    <Parameter Name="Opentime" Type="timestamp" Mode="In"  />
</Function>

<Function Name="BranchInsert" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false"
          ParameterTypeSemantics="AllowImplicitConversion" Schema="CarRentalModel">
      <Parameter Name="Branchid" Type="integer" Mode="In" />
      <Parameter Name="Opentime" Type="timestamp" Mode="In" Precision="2" />
</Function>

ADO.NET Entity Data Model Designer allows storage model editing in the XML Editor. However, any update to the model with the Update Wizard will discard all manual changes made to the storage model.

The SQL Engine does not return Stored Procedure result-set metadata. Hence, an imported function, will not have a return type or collection associated with it. After the function is imported into the model, a user can bring up the Add or Edit Function Import wizard from Model Browser and change the return type as required.

Visual Studio code generation is supported for Function Imports only for stored procedures that return a collection of an EntityType when the target framework is .NET Framework 3.5. And, the columns returned by the corresponding stored procedures must exactly match the properties of the returned EntityType. If the method should return nothing or a collection of scalars, the target framework has to be .NET Framework 4.0 or above for the code generation to work.

In the example below, the stored procedure returns the unit price of a product by the product id it gets as a parameter.

CREATE PROCEDURE GetUnitPriceForProduct(in pId integer)
DYNAMIC RESULT SETS 1
BEGIN
    declare cur1 cursor with return only to client for
    Select UnitPrice From Product Where ProductID = :pId;
    open cur1;
END;

Step 1: Add a FunctionImport for the stored procedure.
In the designer press the right mouse button and click on the Add -> Function Import from the menu. The Add Function Import dialog will be open and insert the following details:

Press OK to end this process. After step 1, the FunctionImport element will be added to the CSDL. However, it cannot be used from the object context because no code will be generated for it.

Step 2: Use EntityClient to query the data. The following code shows how to open a connection, create a stored procedure command, add parameters to it and execute it using EntityClient.

static void Main(string[] args)
{
    decimal result = 0;
    using (var conn = new EntityConnection("Name = EDMExample"))
    {
         var cmd = conn.CreateCommand();
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.CommandText = "EDMExample.GetUnitPriceForProduct";
         cmd.Parameters.AddWithValue("pId", 1);
         conn.Open();
         result = Convert.ToDecimal(cmd.ExecuteScalar());
    }
    Console.WriteLine("Total = {0}", result);
}

• Store functions are not supported by Function Import and cannot be used for mapping entity insert, delete, and update operations.