The return value from a stored procedure gets the first character only in ASP.NET

Question

When getting a return value from a stored procedure, it only returns the first character,

Exec sp_Auto_Gen_TTBDBatNo 'TT', '' in SQL Server gets the whole string, but in ASP.NET it gets the first character.

How do I get the whole string value?

CREATE PROC sp_Auto_Gen_TTBDBatNo
      @Prefix nvarchar(2),
      @Result nvarchar(8) output
AS
BEGIN
    DECLARE @LastValue int


    -- CompanyCode = @CompanyCode AND BankCode = @BankCode AND AccountCode = @AccountCode

    SET NOCOUNT ON
    If @Prefix = 'BD'
        SELECT @LastValue = MAX(RIGHT(RTRIM(ISNULL(BatchNo, '')),2)) from dbo.Cheque_IssueRecord_Secretary_Review_BD WHERE ISNUMERIC(RIGHT(RTRIM(BatchNo),2))= 1 AND LEN(RIGHT(RTRIM(BatchNo),2)) = 2
    ELSE
        SELECT @LastValue = MAX(RIGHT(RTRIM(ISNULL(BatchNo, '')),2)) from dbo.Cheque_IssueRecord_Secretary_Review_TT WHERE ISNUMERIC(RIGHT(RTRIM(BatchNo),2))= 1 AND LEN(RIGHT(RTRIM(BatchNo),2)) = 2

    SET NOCOUNT OFF
    set @Result = @Prefix + RIGHT(RTRIM(STR(year(getdate()))),2)+RIGHT('0'+LTRIM(RTRIM(STR(month(getdate())))),2) + RIGHT('0'+LTRIM(RTRIM(STR(ISNULL(@LastValue,0)+1))),2)

    print @Result
END

C# code:

string tAuto_Batch = "";

SqlTransaction trans = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
    try
    {
        SqlCommand command = new SqlCommand("sp_Auto_Gen_TTBDBatNo", connection);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add(new SqlParameter("@Prefix", "TT"));
        //command.Parameters.Add(new SqlParameter("@CompanyCode", cheque.Voucherbatchno));
        //command.Parameters.Add(new SqlParameter("@BankCode", cheque.Voucherno));
        //command.Parameters.Add(new SqlParameter("@AccountCode", cheque.Voucherno));

        SqlParameter ResultValue = new SqlParameter("@Result", tAuto_Batch);
        ResultValue.Direction = ParameterDirection.Output;

        command.Parameters.Add(ResultValue);

        connection.Open();
        trans = connection.BeginTransaction();
        command.Transaction = trans;
        command.Connection = connection;

        command.ExecuteNonQuery();
        trans.Commit();

        tAuto_Batch = command.Parameters["@Result"].Value.ToString();

        command.Dispose();
        trans.Dispose();
        connection.Close();

    }
    catch (Exception ex)
    {
        connection.Close();
        Error_Label.Text = Error_Label.Text + "sp_Auto_Gen_TTBDBatNo error " + ex.Message;
    }
}

Answer 1

Make sure you really use it like this:

@Result NVARCHAR(8) OUTPUT
SqlParameter resultValue = new SqlParameter("@Result", SqlDbType.NVarChar, 8);

The default length for (N)VARCHAR columns is 1.

Answer 2

According to MSDN:

For output parameters with a variable length type (nvarchar, for example), the size of the parameter defines the size of the buffer holding the output parameter. The output parameter can be truncated to a size specified with Size.

So it's important to specify the size for out parameters.