title: Using Dapper.net to call stored procedure, always return -1 back
body: <p>here's my stored procedure.  when I test it, always get correct result back.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[AddSmoothieIngredients]
      -- Add the parameters for the stored procedure here
      @Query NVARCHAR(4000) ,
      @SmoothieId INT ,
      @CreatedDate DATETIME ,
      @Status INT ,
      @UserId INT
  AS 
      BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
          SET NOCOUNT ON;
  
          BEGIN TRY
              BEGIN TRAN
  
              IF @SmoothieId &gt; 0 
                  BEGIN
                      DELETE  FROM dbo.SmoothieIngredients
                      WHERE   SmoothieId = @SmoothieId;
  
                      EXECUTE (@Query);
                  END
              ELSE 
                  BEGIN
  
                      IF @UserId = 0 
                          SET @UserId = NULL;
  
                      INSERT  INTO dbo.Smoothie
                              ( Name, CreatedDate, Status, UserId )
                      VALUES  ( N'', @CreatedDate, @Status, @UserId );
  
                      SET @SmoothieId = SCOPE_IDENTITY();
  
                      SET @Query = REPLACE(@Query, 'sId', @SmoothieId);
                      EXECUTE (@Query);
  
                  END 
  
  
              COMMIT TRAN
  
              RETURN @SmoothieId
  
          END TRY
  
          BEGIN CATCH
              ROLLBACK
          END CATCH
  
      END
  </code></pre>
  
  <p>However When I call this stored procedure using dapper.net, always return -1 back.</p>
  
  <pre><code>        using (var conn = OpenConnection())
          {
              var parameter = new { Query = query, SmoothieId = smoothieId, CreatedDate = createdDate, Status = status, UserId = userId  };
              return conn.Execute("AddSmoothieIngredients", parameter, commandType: CommandType.StoredProcedure);
          }
  </code></pre>
  
  <p>Probably, dapper.net cannot pick up the return value from the stored procedure.  but I really dont know how to fix it. please help. </p>