Declare the length of a MSSQL output parameter called from C#

Question

I have a mssql store procedure with two parameters input, output.

CREATE PROCEDURE SP_Test1
        @Pi_Action          INT,
        @Pv_ErrorMessage    VARCHAR(250)    OUTPUT

I am executing this procedure from my application in this way.

DbManager dbManager = new DbManager();
dbManager.ProcName = "SP_Test1";
dbManager.addParam("@Pi_Action", SqlDbType.Int, ParameterDirection.Input, 3);
dbManager.addParam("@Pv_ErrorMessage", SqlDbType.VarChar, ParameterDirection.Output, null);
        bool resultado = dbManager.executeProc();

I have a class called DbManager with two methods.

    public void addParam(string paramName, SqlDbType paramType, ParameterDirection paramDirection, object paramValue)
    {
      parametros.Add(new Parametro(paramName, paramType, paramDirection, paramValue));
    }

    public bool executeProc()
    {
        using (SqlConnection sqlConnection = new SqlConnection(connStr))
        {
           SqlCommand sqlComm = new SqlCommand();
           sqlComm.Connection = sqlConnection;
           sqlComm.CommandText = ProcName;
           sqlComm.CommandType = CommandType.StoredProcedure;

           SqlParameter sqlPar = null;
           foreach(Parametro parametro in parametros)
           {
              sqlPar = new SqlParameter();
              sqlPar.ParameterName = parametro.paramName;
              sqlPar.SqlDbType = parametro.paramType;
              sqlPar.Direction = parametro.paramDirection;
              sqlPar.Value = parametro.paramValue;
              //sqlPar.Size = 300;
              sqlComm.Parameters.Add(sqlPar);
           }
           sqlConnection.Open();
           sqlComm.ExecuteNonQuery();

           String parVal = (String)sqlComando.Parameters["@Pv_ErrorMessage"].Value;

           return true;
         }

In the time that I get the value of the output parameter, this returns me only the first letter of the string. I must always declare the length of the string parameters? Exists a way I can avoid to declare the length of the string parameters and return me the whole string?

Thanks for your help.

just out of curiosity, if you pause your application at the String parVal line and queries the content of sqlComando.Parameters["@Pv_ErrorMessage"].Value on your Immediate Window, what's the response? Is it a one-char string as well? — OnoSendai, Nov 26 '13 at 17:50
You'll find some info [here](http://stackoverflow.com/questions/3759285/ado-net-the-size-property-has-an-invalid-size-of-0) and [here](http://stackoverflow.com/questions/14342954/ado-net-safe-to-specify-1-for-sqlparameter-size-for-all-varchar-parameters) — , Nov 26 '13 at 18:14
As an aside, the SqlCommand creation needs to be added to a using statement as well. — Dan Lyons, Nov 26 '13 at 18:59

score 0 · Answer 1 · answered Nov 26 '13 at 18:24

The SqlParameter class supports a Size property that you're not setting. Create a new overload for your addParam method that accepts the size in addition to the existing info you're passing and then add the following...

sqlPar.Size = yourNewMethodParameterName

Declare the length of a MSSQL output parameter called from C#

1 Answers1