How to update multiple rows of a table through a stored procedure?

Take this image of the table as the table on which I need to perform the multiple update operations I have an employee table with 1000 entries, another table is the department table with 1000 entries as well. I created a mapping table which the contains the primary keys of both the tables and it also has 1000 entries. Now, I have to join all these three tables and perform update procedures to some of the rows but it should be able to update the rows in one go and that also is preferred using a stored procedure. Need some help!

I am aware about using the table variable for doing so, But I need to understand how to write the script for creating the stored procedure and doing the same.

Thanks.

Solution

As many have suggested in the comments, you should absolutely read up on how UPDATE works in SQL Server, as this will do everything it sounds like you need.

In any case, here's an example to help explain this for you. First, let's create a table Employees and populate it with some records:

create table Employees
(
    id int,
    first_name varchar(50),
    last_name varchar(50),
    starting_date date,
    last_checked date
)

insert into Employees values 
(1, 'John', 'Smith', '2016-05-16', null),
(2, 'Sandra', 'Evans', '2013-02-02', null),
(3, 'Phil', 'Jones', '2019-10-25', null),
(4, 'Jenny', 'Lewis', '2018-07-11', null),
(5, 'Steve', 'Daniels', '2018-02-28', null)

Now we're able to select all the rows out of this table and see the data:

select * from Employees

RESULTS:
/------------------------------------------------------------\
| id | first_name | last_name | starting_date | last_checked |
|----|------------|-----------|---------------|--------------|
| 1  | John       | Smith     | 2016-05-16    | NULL         |
| 2  | Sandra     | Evans     | 2013-02-02    | NULL         |
| 3  | Phil       | Jones     | 2019-10-25    | NULL         |
| 4  | Jenny      | Lewis     | 2018-07-11    | NULL         |
| 5  | Steve      | Daniels   | 2018-02-28    | NULL         |
\------------------------------------------------------------/

Now, if we wanted to update every row in the table, for example to set a value in the last_checked column, this can be done with a simple UPDATE statement:

update Employees set last_checked = getdate()

This updates every record in the table:

select * from Employees

RESULTS:
/------------------------------------------------------------\
| id | first_name | last_name | starting_date | last_checked |
|----|------------|-----------|---------------|--------------|
| 1  | John       | Smith     | 2016-05-16    | 2020-01-21   |
| 2  | Sandra     | Evans     | 2013-02-02    | 2020-01-21   |
| 3  | Phil       | Jones     | 2019-10-25    | 2020-01-21   |
| 4  | Jenny      | Lewis     | 2018-07-11    | 2020-01-21   |
| 5  | Steve      | Daniels   | 2018-02-28    | 2020-01-21   |
\------------------------------------------------------------/

If desired, you can equally use a WHERE clause to determine which records should be updated:

update Employees set last_checked = '2019-01-01' where starting_date <= '2019-01-01'

select * from Employees

RESULTS:
/------------------------------------------------------------\
| id | first_name | last_name | starting_date | last_checked |
|----|------------|-----------|---------------|--------------|
| 1  | John       | Smith     | 2016-05-16    | 2019-01-01   |
| 2  | Sandra     | Evans     | 2013-02-02    | 2019-01-01   |
| 3  | Phil       | Jones     | 2019-10-25    | 2020-01-21   |
| 4  | Jenny      | Lewis     | 2018-07-11    | 2019-01-01   |
| 5  | Steve      | Daniels   | 2018-02-28    | 2019-01-01   |
\------------------------------------------------------------/

Note how every last_checked date except the one for Phil Jones was updated, because of the where starting_date <= 2019-01-01 clause in the update statement.

Hopefully that demonstrates the basics of an update statement, which can of course be wrapped in a Stored Procedure if you need:

create procedure [dbo].[updateEmployeeLastChecked] 
    @newDate date = null
as

if @newDate is null set @newDate = getdate()
update Employees set last_checked = @newDate where starting_date <= @newDate

So in this instance, the stored procedure takes an optional parameter (which defaults to the current date if not supplied) specifying the date to be used when updating the records in the Employees table, provided the date specified is after the starting_date for each employee. Not a particularly useful or realistic procedure, but it demonstrates the principle hopefully.