21

I have a stored procedure with an output parameter. How do I read this value using C# code?

JYelton
  • 35,664
  • 27
  • 132
  • 191
shmandor
  • 881
  • 3
  • 14
  • 22

1 Answers1

41

I assume you use ADO.NET? If so, the SqlParameter class has the property "Direction". Set direction to output and after the query has executed you read the value from that parameter.

Something like this:

using (SqlCommand cmd = new SqlCommand("MyStoredProcedure", cn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter parm = new SqlParameter("@pkid", SqlDbType.Int);
    parm.Value = 1;
    parm.Direction = ParameterDirection.Input;
    cmd.Parameters.Add(parm);

    SqlParameter parm2 = new SqlParameter("@ProductName", SqlDbType.VarChar);
    parm2.Size = 50;
    parm2.Direction = ParameterDirection.Output; // This is important!
    cmd.Parameters.Add(parm2);

    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
}
Merrimack
  • 1,736
  • 14
  • 12
  • i have another question i need to determine that output parameter is decimal(8,2) how!!! – shmandor Aug 08 '10 at 09:37
  • I am not sure I understand the question. If you are returning a decimal in the output variable you should set the SqlDbType to Decimal. If you are in fact returning a decimal you can cast like this: (decimal)cmd.Parameters[@"MyDecimal"].Value – Merrimack Aug 08 '10 at 09:46
  • 6
    I would strongly suggest to put `SqlConnection` and `SqlCommand` into `using(....) { ... }` blocks as a best practice – marc_s Aug 08 '10 at 09:50
  • i have problem when call stroc embeded or execution nested stroc – shmandor Aug 08 '10 at 10:41