Return data for two columns from two tables and perform a calculation on them with stored procedure

A mini employee accounts system, I have two tables, the employees_salaries table and the allowance table, each one has a column of employee_id To connect them.

I want to return the value in the basic_salary column from employees_salaries table
and return the value in the allowance_value column from allowance table
and make sum operation on them and insert the result into Net_salary column in employees_salaries table.

i want to make that with Stored Procedure Sql server .

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Create a Scalar Function and return the allowance amount from the allowance table

CREATE FUNCTION dbo.GetValue(@EmpID INT)
RETURNS INT
AS 
SELECT allowance_value
FROM [allowance table]
WHERE E_ID = @EmpID

And then Alter the Employees Salary table & add a new Column

By passing the EmployeeId, you Can get the data

ALTER TABLE dbo.employees_salaries 
ADD NewColumnName AS (dbo.GetValue(employees_salaries.[EmpId Column]) + employees_salaries.[Basic pay Col])

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0