How to update a table using stored procedures in SQL Server

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

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?

-- =============================================
-- Author:      XYZ
-- Create date: xx-xx-xxxx
-- Description: Procedure for Updating  Emp Detail
-- =============================================


CREATE PROCEDURE [dbo].[SP_EmpLoyee_Update]
(
@EmpCode bigint=null,
@EmpName nvarchar(250)=null,
@MNumber nvarchar(250)=null,
@Status int=null,
@LoginUserId nvarchar(50)=null,
@Msg nvarchar(MAX)=null OUTPUT
)
AS
BEGIN TRY

UPDATE tbl_Employees
SET
EmpName=@EmpName,
MNumber=@MNumber,
Status=@Status,
ModificationDate=GETDATE()

WHERE EmpCode=@EmpCode

    SET @Msg='Employee   Updated Successfully.'

END TRY
BEGIN CATCH

    SET @Msg=ERROR_MESSAGE()

END CATCH

GO

*Try below code

Create Procedure UpdateRecord (@emp_code int)
as
begin 
update employee set status= 'Y'
 where emp_code=@emp_code
end

*execute as below

exec UpdateRecode 3

3 is your emp_code. please change as your requirement.

drop procedure if exists updateBillerContactInfo1;
DELIMITER $$
CREATE PROCEDURE updateBillerContactInfo1 (INOUT account_id_list varchar(4000))
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE first_name varchar(255) DEFAULT "";
    DECLARE last_name varchar(255) DEFAULT "";
    DECLARE db_id int(10) DEFAULT 0;

-- declare cursor
DEClARE curContact 
    CURSOR FOR 
    select contact.first_name, contact.last_name, acc.db_id from 
    ...(table and conditions)limit 100;

DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET finished = 1;

OPEN curContact;

updateContact: LOOP
    FETCH curContact INTO first_name, last_name, db_id;
    IF finished = 1 THEN 
        LEAVE updateContact;
    END IF;
    -- to update in stored procedure, use stored statement, do not use UPDATE WHERE directly, it cause issue
    SET @updateText = CONCAT('UPDATE iam_capability.t_account 
        SET first_name = ?, last_name = ? 
        WHERE db_id = ?;');
    PREPARE stmt1 FROM @updateText;
    SET @fn = first_name;
    SET @ln = last_name;
    SET @id = db_id;
    EXECUTE stmt1 USING @fn, @ln, @id;
    DEALLOCATE PREPARE stmt1;
    -- build update account id list
    SET account_id_list = CONCAT(db_id, ",",account_id_list);
END LOOP updateContact;
CLOSE curContact;

END$$
DELIMITER ;

-- use transaction to run the script 
start transaction;
SET @account_id_list = ""; 
CALL updateBillerContactInfo1(@account_id_list); 
SELECT @account_id_list;

-- check if data get updated as expected, yes run commit, no, run Rolback

commit;

ROLLBACK;