The following are general guidelines when using stored procedures:

• Stored Procedure can be executed by setting the TdCommand.CommandType to CommandType.StoredProcedure and

  • Setting the TdCommand.CommandText to the stored procedure name.
    Example: cmd.CommandText = "STOREPROC1"
    If the CommandText is set to the stored procedure name, the .NET Data Provider will generate the CALL statement.
  • Setting the TdCommand.CommandText to the fully defined CALL statement.
    Example: cmd.CommandText = "CALL STOREPROC1 (param1, param2)"
    

• The preferred method to execute a stored procedure is to set the TdCommand.CommandText to the stored procedure name, and TdCommand.CommandType to CommandType.StoredProcedure.

• When the Session Mode is ANSI, do not set the CommandText to a CALL <stored procedure> statement and CommandType to CommandType.Text. When this request is submitted to the Advanced SQL Engine, a TdException will be thrown.

  The reason this occurs is that the provider is unaware that a stored procedure is to be executed and will append a COMMIT statement to the request. Teradata requires that the CALL statement be the last statement in a multi-statement request.

  If the CommandText is set to the name of the stored procedure or the CALL statement and CommandType is set to CommandType.StoredProcedure, the provider knows a stored procedure is to be executed and will correctly submit the request to Teradata.

• When retrieving the data of INOUT and OUT parameters, the TdParameter.Value property will always contain the FCL (Framework Class Library) data type representation of the returned value.

• The data type returned from the TdParameter.ProviderSpecificValue property will depend on whether the parameter maps to one of the Provider Specific types (TdDecimal, TdDate, etc...) and the setting of the corresponding Enable Connection String Attribute. If the Enable Connection String Attribute is set to true (this is the default), the Provider Specific type representation will be returned. If false the FCL data type representation is used.

• External stored procedures do not support inline return of LOB data for INOUT or OUT parameters. When calling an external stored procedure, an application must use one of these methods:

  1. TdCommand.ExecuteReader()
  2. TdCommand.ExecuteNonQuery(). The TdParameter.TdType must be set to CLOB/BLOB for INOUT/OUT parameters.

  Application should not set:

  1. CommandBehavior to SequentialAccess when using TdCommand.ExecuteReader
  2. TdParameter.TdType to VarChar/VarByte for the CLOB/BLOB INOUT/OUT parameters when using TdCommand.ExecuteNonQuery
  
  DBS will return "6950 - Returning LOB inline in multipart Record mode is not supported for XSP" error as .NET Data Provider will try to retrieve the BLOB/CLOB data in inline mode in such cases.

See Also

Stored Procedure Under UTF8 Guidelines

Provider Specific Types Overview

Enabling Provider Specific Types

TdParameter.Size

TdParameter.Value

TdParameter.ProviderSpecificValue