ExecuteScalarAsync() does not return the same value returned from stored procedure

Question

I have a stored procedure that return either a positive integer or -1. I have this code call that stored procedure and I don't know why it could not capture the value returned from the stored procedure

    SqlConnection sqlConn = GetNewSqlConnection();
    await sqlConn.OpenAsync();

    try
    {
        var ret = await sqlConn.ExecuteScalarAsync<int>(
                "dbo.Usp_Comment_Update",
                new
                {
                    CommentID_in = model.CommentID,
                    Comment_in = model.Comment,
                    CommentTypeID_in = model.CommentTypeID,
                    SortIndex_in = model.SortIndex,
                    UpdatedBy_in = model.UpdatedBy
                },
                commandType: CommandType.StoredProcedure);

        return (int)ret;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (sqlConn.State != ConnectionState.Closed)
        {
            sqlConn.Close();
        }

        sqlConn.Dispose();
    }

Stored procedure

DECLARE @RowAffected    INT
SET NOCOUNT ON;

UPDATE dbo.DB_Comments 
SET  [Comment]       =  @Comment_in 
    ,[CommentTypeID] =  @CommentTypeID_in
    ,[SortOrder]     =  @SortIndex_in
    ,[UpdatedBy]     =  @UpdatedBy_in
    ,[UpdatedDate]   =  GETDATE()
WHERE CommentID = @CommentID_in

SET @RowAffected = @@ROWCOUNT
PRINT @RowAffected 

IF @RowAffected > 0
    RETURN @RowAffected 
ELSE
    RETURN -1

The above codes always return 0. I read one of the posts here that suggested to see what the return data type is. So I removed after ExecuteScalarAsync and looked for variable ret in Watch window and it was a null Object. The connection string is correct and it connects to the right database as after the call, the data is updated as expected. I even tried to remove everything in the stored procedure except for RETURN -1, but I still got 0 from ExecuteScalarAsync.

What did I do wrong, here?

you should use `parameters`, see [Getting return value from stored procedure in ADO.NET](https://stackoverflow.com/questions/3309213/getting-return-value-from-stored-procedure-in-ado-net) — Lei Yang, Jul 01 '21 at 02:49
`ExecuteNonQueryAsync` should get you the correct result, for this procedure, although best practice is like the given answer — Charlieface, Jul 01 '21 at 08:15

tontonsevilla · Accepted Answer · 2021-07-01T03:23:46.197

1

Remove this SET NOCOUNT ON in your stored procedure to get the proper value of the @@ROWCOUNT.

For more details please check this SET NOCOUNT (Transact-SQL)

Don't use RETURN -1 but rather use SELECT -1. Scalar value is a table like result with single value (1 row 1 column).

edited Jul 01 '21 at 03:23

answered Jul 01 '21 at 03:08

tontonsevilla

2,649
1
11
18

2

[The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver15#remarks) – Charlieface Jul 01 '21 at 08:15
Yes. I left SET NOCOUNT ON and it still returned value. I learnt new things today. Thanks guys. – T L Jul 01 '21 at 16:50

ExecuteScalarAsync() does not return the same value returned from stored procedure

1 Answers1