how to update record by id using stored procedure

somesh kunder

5y817.NET

plz update

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
Create SP like sample shown:
CREATE PROCEDURE [dbo].[SpUpdateTable]
@Id bigint
           ,@RegionId bigint
           ,@Code nvarchar(150)
           ,@Name nvarchar(150)
           ,@Active bit
           ,@Editor bigint
           ,@Modified datetime
           ,@Author bigint
           ,@Created datetime

AS
BEGIN

                UPDATE [dbo].[TableName] WITH(XLOCK,ROWLOCK)
                SET
                        [RegionId] = @RegionId
                       ,[Name]= @Name
                       ,[Code]=@Code
                       ,[Active] = @Active
                       ,[Editor] = @Editor
                       ,[Modified] = @Modified
                WHERE Id = @Id
                SELECT @Id;


END
GO
Execute it in your code as shared by Sachin.
Yes, You can update multiple records in one action.
+2
somesh kunder5y ago
one or more table record update with one action result
+1
Sachin Singh5y ago
Stored Procedure
create procedure spUpdateEmployee
  @Id int ,
  @Name varchar(50),
   @DepId int

  as
  begin
  update tblEmployee set Name=@Name, DepartmentId=@DepId where Id=@Id
  end
protected void Button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                //Create the SqlCommand object
                SqlCommand cmd = new SqlCommand("spUpdateEmployee", con);
                 //Specify that the SqlCommand is a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;
                              //Add the input parameters to the command object
                cmd.Parameters.AddWithValue("@Id",txtId.Text);
              cmd.Parameters.AddWithValue("@Name",txtName.Text);
                cmd.Parameters.AddWithValue("@DepId", ddlDepartment.SelectedItem.Value);
              //Open the connection and execute the query

                con.Open();
                cmd.ExecuteNonQuery();

            }
        }
please refer https://www.sharpencode.com/article/ADONET/stored-procedure-in-ado-net
+1
Nikunj Satasiya5y ago
Hi Somesh, Please try this procedure, to update the record based on ID, at the time of calling this store procedure pass ID as Peramiter.
CREATE PROCEDURE YourProcedureName
   @ID INT = 0
AS
BEGIN
   UPDATE tblYourTableName WITH(NOLOCK) SET YourColumnName = 'Updated Value' WHERE Id = @ID
END
Calling Procedure
EXEC YourProcedureName 3
Where 3 is your parameter ID.

For more information about the stored procedure you can visit these articles:

https://www.codingvila.com/2018/11/how-to-pass-output-parameter-to-stored-procedure-in-microsoft-sql-server.html

https://www.codingvila.com/search/label/Stored%20Procedure

Thank you.
+1