How to update salary for some employees by using Stored Procedure?

Question

I have a EMP table having the following data:

id,name,sal

1 ABC 1000

2 DEF 950

3 GHI 1100

I want to update salary for id 2 and 3 to 1000 and 1200 respectively by writing SP in SQL? Is itpossible?

Answer 1

Hi @Ramana Kopparapu

Please check this sample:

CREATE TABLE TableA (id int,name varchar(20),sal int) 
INSERT INTO TableA VALUES 
(1,'ABC',1000),
(2,'DEF',950),
(3,'GHI',1100)

GO
CREATE PROC sp_Test
  @id INT,   
  @salary INT    
AS 
UPDATE TableA
SET sal = @salary
WHERE id = @id;

EXEC sp_Test @id=2, @salary=1000

EXEC sp_Test @id=3, @salary=1200

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

CREATE PROCEDURE UpdateSalary
    @id1 INT,
    @salary1 INT,
    @id2 INT,
    @salary2 INT
AS
BEGIN
    UPDATE EMP
    SET sal = CASE
        WHEN id = @id1 THEN @salary1
        WHEN id = @id2 THEN @salary2
        ELSE sal
    END
    WHERE id IN (@id1, @id2);
END;

In this example, the stored procedure UpdateSalary takes four parameters: @id1, @salary1, @id2, and @salary2. You can call this stored procedure with the desired IDs and salary values to update the corresponding rows in the EMP table.

EXEC UpdateSalary @id1 = 2, @salary1 = 1000, @id2 = 3, @salary2 = 1200;