Using Dapper.net to call stored procedure, always return -1 back

Question

here's my stored procedure. when I test it, always get correct result back.

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 > 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

However When I call this stored procedure using dapper.net, always return -1 back.

        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);
        }

Probably, dapper.net cannot pick up the return value from the stored procedure. but I really dont know how to fix it. please help.

score 22 · Answer 1 · edited May 23 '17 at 11:54

22

So the reason that Execute() returns -1 is because your sproc has SET NOCOUNT ON; which "suppresses the "xx rows affected" message after any DML" according to this question. Whether you want to disable that or not is another question also discussed in that link.

I just came across the same problem so I thought I'd throw in my 2 cents.

edited May 23 '17 at 11:54

Community

1
1

answered Feb 04 '14 at 04:58

joakes

221
2
7

Never knew this! +1 – nawfal Feb 13 '18 at 16:17
Thank you! I was pulling my hair out after creating an INSERT stored procedure based on a SELECT one and didn't remove the SET NOCOUNT ON; line. – DotNetDublin Apr 22 '21 at 09:57
Bless your soul, I've been searching this for over 20 minutes :) – L. Mihai Aug 11 '21 at 06:46

score 15 · Answer 2 · answered Sep 10 '12 at 00:09

Found the solution, here's the sample code I found online. and it works.

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: commandType.StoredProcedure); 

int b = p.Get<int>("@b");
int c = p.Get<int>("@c");

Zeph · Answer 3 · 2012-09-10T00:03:09.917

It looks like Dapper.net uses the SqlCommand.ExecuteNonQuery for the Execute method. This returns the number of rows affected, not the value of the return statement. What you're looking for is Query

return connection.Query<int>("AddSmoothieIngredients", parameter, commandType: CommandType.StoredProcedure).First();

Although I don't think that will capture the return statement either, in which case you'll need to alter the stored procedure to return a results set.

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 > 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

        SELECT @SmoothieId
        RETURN
    END TRY

    BEGIN CATCH
        ROLLBACK
    END CATCH

END

Or you could use another DB access method.

thanks. but you still have to use execute, I found another sample online, and it works. — qinking126, Sep 10 '12 at 00:10

takrl · Answer 4 · 2016-08-29T09:11:54.767

Recently I had to change an existing procedure to additionally return a value, and I had been using an anonymous type to pass the parameters. In this context, the really nice thing is that DynamicParameters supports an anonymous type as an input parameter, which makes this change easy to implement.

I had the following:

cnn.Execute("spMagicProc", new { a = 11, x = 13, y = 14, z = "something" },
  commandType: commandType.StoredProcedure);

I was able change that to:

var p = new DynamicParameters(new { a = 11, x = 13, y = 14, z = "something" });
p.Add("@rval", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: commandType.StoredProcedure);

int rval = p.Get<int>("@rval");

Using Dapper.net to call stored procedure, always return -1 back

4 Answers4