title: Call stored procedure and return a message
body: <p>I have the following SQL Server stored procedure (declared parameters are missing but I don't have problems with that):</p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE @TotalRegistros AS INT = 0
  DECLARE @ShowMsg AS NVARCHAR(50)    
  
  SELECT @TotalRegistros = COUNT(*) 
  FROM Tbl_Productos 
  WHERE IdProducto = @Producto
  
  IF @TotalRegistros &lt; 3
  BEGIN
      INSERT INTO Tbl_Productos (IdProducto, UnidadMedida, Precio, Activo) 
      VALUES (@Producto, @Medida, @Precio, @Activo)
  
      SET @ShowMsg = 'All fine!'
  END
  ELSE
  BEGIN
      SET @ShowMsg = 'Error, you already have 3 rows.!'
  END
  
  SELECT @ShowMsg
  </code></pre>
  
  <p>I execute this stored procedure with this C# code:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public string ExecuteSP(Producto prod)
  {
      int res = 0;
      string resp = "";
  
      SqlCommand cmd = new SqlCommand("SPName", conn);
  
      try
      {
          Open();
          cmd.CommandType = CommandType.StoredProcedure;
  
          cmd.Parameters.AddWithValue("@Producto", prod.Id);
          cmd.Parameters.AddWithValue("@Medida", prod.Tipo);
          cmd.Parameters.AddWithValue("@Precio", prod.Precio);
          cmd.Parameters.AddWithValue("@Activo", prod.Active);
  
          //CodeUpdated
          SqlParameter returnMessage = new SqlParameter("@ShowMsg", SqlDbType.NVarChar);
          returnMessage.Direction = ParameterDirection.ReturnValue;
  
          cmd.Parameters.Add(returnMessage);
  
          res = cmd.ExecuteNonQuery();
  
          resp = returnMessage.Value.ToString();
      }
      finally
      {
          Close();
      }
  
      return resp;
  }
  </code></pre>
  
  <p>I know that I need to add something like <code>OutputParameter</code> and add the <code>@ShowMsg</code> but I don't know exactly how to do it! and the main problem is that I execute this code from a <strong>WebMethod</strong> with this code:</p>
  
  <pre class="lang-cs prettyprint-override"><code>[WebMethod]
  public string ExecuteC#Code(int id, string medida, string precio, string activo)
  {
      string respuesta = "Error";
  
      try
      {
          Producto prod = new Producto(id, medida, precio, activo);
          //Code updated
          string resp = conn.ExecuteSP(prod);
  
          if (resp == "All fine!")
              respuesta = "Operacion realizada existosamente";
          else
              respuesta = "Already 3 rows with same Id..."
      }
      catch (Exception ex)
      {
          respuesta = "A ocurrido un error: " + ex.Message;
      }
  
      return respuesta;
  }
  </code></pre>
  
  <p>Like I said, I have an idea of what I need to do to get the message after execute the stored procedure, but then I don't have any idea of how can I send that message to my web method. I know that I need to change the data type of my methods because I'll expect a <code>string</code> instead of an <code>int</code>.</p>