CODEWITHSUNDEEP
c#.netsql-serverstored-procedures
developer
developer

Reputation: 11

Update stored procedure not working in SQL Server

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

Upvotes: 0

Views: 1379

Answers (1)

Thom A
Thom A

Reputation: 96015

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;

Upvotes: 1

Related Questions