stored procedure drops the leading zero while executing

[samuelkang]

I have created an insert stored procedure as follows

ALTER PROCEDURE dbo.rcPersistOrderDetail
(
@Store AS varchar(5),
@tDate AS varchar(8),
@ToB AS varchar(2),
@UVNDCDE AS varchar(8),
@QUANTITY AS varchar(8)
)
AS
BEGIN
Declare @SQL VarChar(1000)
SELECT @SQL = 'INSERT INTO [' + @Store + '] (Store, tDate, ToB, UVNDCDE, QUANTITY) '
SELECT @SQL = @SQL + ' Values(' + @Store + ',' + @tDate + ',' + @ToB + ','
SELECT @SQL = @SQL + @UVNDCDE + ',' + @QUANTITY + ')'
Exec(@SQL)
END
RETURN

When this stored procedure is executed for example with
the following insert values
8888, 08252005 ,14,124,5
the inserted result is
8888, 8252005 ,14,124,5

Yes, the tDate field in the table is defined as varchar(8).

I am using c# and ado.net to execute the stored procedure.
When I define the ado.net command as of type command text
and actually assign the sql statement to the command object,
I get the desired result. I just can't figure out why the stored procedure would behave differently. Please help!

[skhanal]

When you pass the parameters in ado.net did you define the parameter with char type?. I suspect it is getting converted to number when passed to the stored procedure by ado.net. You can confirm that by putting a print or select statement for @tdate in the procedure.

[samuelkang]

I agree with you that somehow the @tdate parameter value is getting converted, however; in ado.net the field is clearly defined as a SqlDbType.VarChar. What I am struggling with
is that fact that instead of executing the stored procedure through ado.net command but to supply the actual sql statement to the command and execute, that I get the correct results. Any ideas? I am including a portion of the ado.net code in c#.

string sqlstr = "SELECT Store, tDate, ToB, UVNDCDE, QUANTITY FROM [8888]";
SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(sqlstr, conn));

sqlstr = "rcPersistOrderDetail";
iCmd = new SqlCommand(sqlstr, conn);
Cmd.CommandType = CommandType.StoredProcedure;
iCmd.Parameters.Add("@Store",SqlDbType.VarChar, 8, "Store");
iCmd.Parameters.Add("@tDate", SqlDbType.VarChar, 8, "tDate");
iCmd.Parameters.Add("@ToB", SqlDbType.Char, 8, "ToB");
iCmd.Parameters.Add("@UVNDCDE", SqlDbType.Int, 8, "UVNDCDE");
iCmd.Parameters.Add("@QUANTITY", SqlDbType.Int, 8, "QUANTITY");

DataSet ds = new DataSet("OrderDetails");
da.FillSchema(ds,SchemaType.Source);
da.InsertCommand = iCmd;

DataRow row = ds.Tables[0].NewRow();
row["Store"] = "8888";
row["tDate"] = "08252005";
row["ToB"] = "14";
row["UVNDCDE"] = 123;
row["QUANTITY"] = 10;
ds.Tables[0].Rows.Add(row);
da.Update(ds);
da.Dispose();

[Alexander]

I think in SP you have two add quotes when additing @tDate:

SELECT @SQL = @SQL + ' Values(' + @Store + ',''' + @tDate + ''',' + @ToB + ','