How to update a table using stored procedures in SQL Server

Question

I have created a table in SQL Server called "Employee", and now I want to update the table using a stored procedure.

The table has emp_name, emp_code and status columns. Assume the table has three records: initially, in the stored procedure I want to fetch the last two records using a select statement and I have to the fetched records' status to 'Y'.

I have written a stored procedure, but it's not affecting the original table. Kindly suggest the query for the stored procedure.

Here is the query I have made:

create  procedure updtdemployee As
  select e.Emp_name ,e.Circle 
  from employee e 
  where e.Emp_Code ='2501' or e.Emp_Code='2502'

  begin
    update employee set status='Y' where Emp_name = employee.Emp_name
  end

Answer 1

You don't need the Select part,just do the update.

CREATE PROCEDURE updtdemployee
       @employeeID INT
  AS
    BEGIN
     UPDATE employee 
     SET status='Y' 
     WHERE Emp_Code = @employeeID
    END

If you want to do it Static you can use this

CREATE PROCEDURE updtdemployee     
      AS
        BEGIN
         UPDATE employee 
         SET status='Y' 
         WHERE Emp_Code = 2501 or Emp_Code = 2502
        END

Answer 2

according to your question "I wants to fetch the last two records" if this is ONLY supposed to ever affect the last 2 records,

CREATE PROCEDURE updtdemployee     
      AS
        BEGIN
         UPDATE employee 
         SET status='Y' 
         WHERE Emp_Code in (select top 2 Emp_Code from employee order by Emp_Code desc)
        END

I am purely guessing on the way you want to order this but is this what you were looking for?