Pass Stored Procedure Output to another Stored Procedure in SQL
Issue-Passing Output of one Procedure as a Input to other Procedure and Displaying RecordsIssue Description-One of my colleagues faced the Issue.Below is the Issue Details
He created two Procedures one was calculating the Basic Annual Salary of an employee and the Second Procedure was calculating TotalSalary which includes basicSalary, bonus which was 10% of Basic Salary and allowances.
So below is a simple example Demonstrating -How to Pass One Stored Proc value to another Stored Procedure
--First Procedure Calculating Basic Salary Per Month
CREATE PROCEDURE [dbo].[BasicAnnualSalary]
@Basic Float
AS
DECLARE @Salary Float
SELECT @Salary = @Basic/12
-- Additional Code
RETURN @Salary
GO
-Second Procedure calculating Total Salary which Includes basic Salary Salary per month,10% of Basic Salary and allowance per mont
Create PROCEDURE [dbo].[TotalSalary]
--First Parameter will Store output of First Procedure that is calculating Basic Salary
@BSalary float,
--Second Parameter is to calculate allowance per month
@Allowance Float
AS
begin
--Tsalary to calculate total salary
DECLARE @TSalary FLOAT
--Bonus to add 10% of BasicSalary
declare @bonus float
--BscSalary to store output of Store Proce BasicAnnual Salary
declare @BscSalary float
exec @BscSalary = [BasicAnnualSalary] @BSalary--Storing first Procedure Output to another Procedure variable
set @bonus =(@BscSalary *10)/100
set @TSalary = (@Allowance/12) +@bonus+@BscSalary
SELECT @TSalary as MonthlySalary,@BscSalary as MonthlyBasicSalary
end
-- Testing by Executing Second Procedure
exec [TotalSalary] 240000,120000
Output
MonthlySalary MonthlyBasicSalary
32000 20000
This is how you can store one Procedure Output to another Procedure
No comments:
Post a Comment