Update stored procedure not working in SQL Server

Question

I have a stored procedure to update the status by changing the bool value from 1 to 0. I am passing the user id to the stored procedure. I just need to update the user status only. But now it's not doing that.

CREATE PROCEDURE [dbo].[UpdateStatus]
    @userID INT
AS
BEGIN
    UPDATE [dbo].[User]
    SET Status = 0 
    WHERE Id = @userID
END

I am calling this stored procedure:

public User UpdateStatus(string UserId)
{
        User userDetails = new User();

        try
        {
            using (SqlConnection sqlConnection = new SqlConnection(connection.GetConnectionString()))
            {
                sqlConnection.Open();
                SqlCommand cmd = new SqlCommand("UpdateStatus", sqlConnection);
                cmd.Parameters.Add(new SqlParameter("@userID", UserId));
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                           userDetails.DisplayName = reader["DisplayName"].ToString();
                    }
                }

                sqlConnection.Close();
            }
        }
        catch(Exception ex)
        {
            //ex.Message;
        }

        return userDetails;
}

I am calling the UpdateStatus function. But it's not updating the status in the database

An UPDATE statement doesn't return a reader to read something — Steve
– Steve, Commented Mar 15, 2021 at 9:18
"but it does not returning anything." Why would it? The Procedure isn't returning a dataset. The only thing you'll get back from that proc would be the RETURN status, which'll likely be 0 for success. — Thom A
– Thom A ♦, Commented Mar 15, 2021 at 9:19
It should be executenonquery. And you should make a select in another query. — MonkeyDLuffy
– MonkeyDLuffy, Commented Mar 15, 2021 at 9:19
Well, if the OP did need data back, they would likely be able to use an OUTPUT clause, @MonkeyDLuffy — Thom A
– Thom A ♦, Commented Mar 15, 2021 at 9:20
@Larnu its not updating the database.so the return status is 0 and expected is 1 — developer
– developer, Commented Mar 15, 2021 at 9:46

Thom A · Accepted Answer · 2021-03-15 09:54:21Z

1

I am basing this answer purely on guess work from this comment:

its not updating the database.so the return status is 0 and expected is 1

An UPDATE that updates 0 rows without an error is still a successful statement . I suspect you are passing a value for @userID where there is no row that has that value for an ID. That won't cause the RETURN status to be 1, or anything else. If you want a failure to occur when no rows are updated then check the value of @@ROWCOUNT. and then THROW or RETURN.

CREATE PROCEDURE [dbo].[UpdateStatus] @userID INT AS
BEGIN
    UPDATE [dbo].[User]
    SET Status=0 
    WHERE Id = @userID;

    --RETURN example
    IF @@ROWCOUNT = 0
        RETURN 1; --Denote "failure"
    ELSE
        RETURN 0; --Denote Success

    --Or perhaps you want to THROW
    IF @@ROWCOUNT = 0
        THROW 89001, N'No rows were updated.',10; --USe a custom Error code appropriate for your application

END;

answered Mar 15, 2021 at 9:54

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

developer Over a year ago

how can i use this @@ROWCOUNT in the c# code.what i have tried is cmd.Parameters.AddWithValue("@userID", UserId); var effectedRows = Convert.ToInt32(cmd.Parameters["@@ROWCOUNT"].Value); this got an exception.

Thom A Over a year ago

Huh? @@ROWCOUNT is a system variable in SQL Server, you can't access it in the C#. It's handled in the Procedure. If you want the value of @@ROWCOUNT returned the you could use an OUTPUT parameter, but that's a completely different question.

developer Over a year ago

will you plz provide any useful meterial for that

Thom A Over a year ago

how to use SQL Server OUTPUT parameters in C# @developer.

Charlieface Over a year ago

ExecuteNonQuery returns the rowcount anyway, so long as NOCOUNT OFF

Collectives™ on Stack Overflow

Update stored procedure not working in SQL Server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related