Calling Function Inside a Stored Procedure in SQL

Calling Function Inside a Stored Procedure in SQL

Below is the Script of Calling a function inside a Stored Procedure.

The Scenario is like we have a function Salary which is performing some calculation on input Salary
in order to get the Exact salary.

Then we have a Procedure that is inserting employeeName and the Calculated Salary by the function in the Employee Table.


--Table Script values will be inserted through Procedure

CREATE TABLE [dbo].[Employee1](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]

GO

--function script that will take Salary as Input and return the calculated salary

create function Salary (@salary float)
returns int
as
begin 
declare @Hra float
set @Hra=@salary /10
declare @Lta float
set @lta=@salary/5
set @salary =@salary+@hra+@lta
return @salary
end

--Stored Procedure Script that will insert into Employee1table with calculated Salary

create proc Employee_insert
(
@EmpName nvarchar(100),
@EmpSalary float)
as begin
insert into Employee1 
values(@EmpName ,dbo.Salary(@EmpSalary))
end

--Executing Procedure
Exec  Employee_insert 'vinod' ,100

OUTPUT

empid empname EmpSalary
1           vinod               130 

Testing Function 

select dbo.salary(100)

Output -130

So the Calculated Salary by the function has been inserted into Employee1 table using Stored Procedure.

NOTE: Above Examples shows how to call a Function inside a Stored Procedure but you cannot can a Stored Procedure inside a function.