Geek asks and answers

How to update a table using stored procedures in SQL Server

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

The table has columns emp_name, emp_codeand status. Suppose there are three records in the table: initially in the stored procedure I want to get the last two records using the select statement, and I have to get the extracted records' statusto 'Y'.

I wrote a stored procedure, but this does not affect the original table. Please offer a request for a stored procedure.

Here is the request I 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
+3
source share
4 answers

You don’t need the “Select” part, just upgrade.

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

Static,

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

" ", - 2 ,

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

, , , ?

+2
-- =============================================
-- 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
+1
source

* Try entering a code

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

* follow as below

exec UpdateRecode 3

3 is your emp_code. Please change your requirement.

0
source

More articles:


All Articles