Using parameters with stored procedure and .Net Provider

Connectivity

Using parameters with stored procedure and .Net Provider

I am connecting to a db server with the .Net Provider. I have a macro that accepts an integer parameter. When I add the parameter, I get a mismatch error:

[Teradata Database] [3939] There is a mismatch between the number of parameters specified and the number of parameters required.

Not sure what I am doing wrong. Here is what I have:

using (TdConnection cn = new TdConnection("Data Source = " + TeraDataServer + ";User ID = " + userid + ";Password = " + password + ";"))
{
cn.Open();
TdCommand command = cn.CreateCommand();
command.Connection = cn;

// Setting up command to make call to stored procedure
command.CommandText = "EXEC AAccounts";
command.Parameters.Add("AcctNum", TdType.Integer);
command.Parameters["LyltyAcctNum"].Value = accountid;

// Going to execute stored procedure
using (TdDataReader dr = command.ExecuteReader())
{
// Retrieving data for first result set.
while (true == dr.Read())
{
accounts.Add(dr.GetInt32(1));
}

// Moving the data reader to the second result set.
dr.NextResult();
}
}

Any help would be greatly appreciated!
2 REPLIES

Re: Using parameters with stored procedure and .Net Provider

I figured out the problem...I need to put "exec AAcounts(?)" in the commandText field to indicate that the macro expects a parameter.
Teradata Employee

Re: Using parameters with stored procedure and .Net Provider

Technically Macros are not identical to Stored Procedures.
Per ADO.NET specification, Macros similar to all other SQL-Statements (not including Stored Procedure) must use parameter markers.

ADO.NET handles Stored Procedure differently. The CommandText must be set to the Stored Procedure Name and the CommandType must be set to CommandType.StoredProcedure.