title: How to obtain returned scalar value of a Stored Procedure using dapper
body: <p>I am very new to dapper-dot-net and I am trying to call a Stored Procedure called <strong>esp_TRV_PERIODO_MES_CRU</strong> which returns an <strong>int</strong> which is the <strong>IdPeriodo</strong> of the inserted/existing/updated record</p>
  
  <p><strong>Please do not remove the comments in my code since they show other approaches that I have tried but not succeded</strong> </p>
  
  <pre><code>DECLARE @return_value int
  
  EXEC    @return_value = [dbo].[esp_TRV_PERIODO_MES_CRU]
          @anio = 2017,
          @mes = 12,
          @mesCerrado = NULL
  
  SELECT  'Return Value' = @return_value
  </code></pre>
  
  <p>I have the following (non-working) implementation </p>
  
  <pre><code>public Tescalar ExecuteScalar&lt;Tescalar&gt;(string spName, DynamicParameters p)
  {
      Tescalar resp ;
  
      resp = default(Tescalar);
  
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
          try
          {
  
              connection.Open();
              resp = connection.ExecuteScalar&lt;Tescalar&gt;(spName, p, null, connection.ConnectionTimeout, commandType: CommandType.StoredProcedure);
              //connection.Execute(spName, p, null, connection.ConnectionTimeout, commandType: CommandType.StoredProcedure);
          }
          catch (Exception ex)
          {
  
              throw ex;
          }
          return resp;
      } 
  }
  </code></pre>
  
  <p>And I am calling the above method like this:</p>
  
  <pre><code>public Int32 ConsultarPeriodoMes(int anio, int mes)
  {
      Int32 idPeriodo;
      DynamicParameters parametros;
      DynamicParameters resp;
  
      parametros = new DynamicParameters();
      parametros.Add("@anio", anio);
      parametros.Add("@mes", mes);
      parametros.Add("@mesCerrado", null);
  
      //idPeriodo = GetDataBaseHelper().ExecuteProcedureNonQuery("esp_TRV_PERIODO_MES_CRU", parametros);
  
      //resp = GetDataBaseHelper().ExecuteProcedure("esp_TRV_PERIODO_MES_CRU", parametros);
      //idPeriodo = -999;
  
      idPeriodo = GetDataBaseHelper().ExecuteScalar&lt;Int32&gt;("esp_TRV_PERIODO_MES_CRU", parametros);
  
  
  
      return idPeriodo;
  }
  </code></pre>
  
  <p>I have tested the Stored Procedure <strong>esp_TRV_PERIODO_MES_CRU</strong> and is returning the expected <strong>idPeriodo</strong> of the INSERTED/FOUND/UPDATED entity, here is the SP</p>
  
  <pre><code>CREATE PROC [dbo].[esp_TRV_PERIODO_MES_CRU]
  @anio INT,
  @mes INT,
  @mesCerrado BIT
  AS
  BEGIN
      DECLARE
      @idperiodo AS INT
  
      --Verfica si el periodo existe, dado los parametros anio y mes 
      SELECT @idperiodo = ISNULL((SELECT IdPeriodoMes FROM TRV_PERIODO_MES(NOLOCK) WHERE Anio=@anio AND Mes=@mes),-1)
  
      IF @idperiodo &gt; -1
          UPDATE [dbo].[TRV_PERIODO_MES]
          SET [CierreEjecutado] = ISNULL(@mesCerrado, 0)
          WHERE IdPeriodoMes = @idperiodo
      ELSE
      BEGIN
          INSERT INTO TRV_PERIODO_MES VALUES(@anio,@mes,ISNULL(@mesCerrado, 0))
          SET  @idperiodo = SCOPE_IDENTITY();
      END
  
      RETURN @idperiodo
  END
  </code></pre>
  
  <p>What do I need to fix in order to be able to obtain the returned value of the Stored Procedure shown above using dapper-dot-net</p>