Calling a Function From a Stored Procedure in SQL Server

In this article, we will see how to call a function from a stored procedure in SQL Server. Here, I have written a scalar function named MultiplyofTwoNumber that accepts two parameters and returns one parameter. Now I want to call this from a stored procedure. So let's take a look at a practical example of how to call a function from a stored procedure in SQL Server. The example is developed in SQL Server using the SQL Server Management Studio.  There are some simple things to do that are described here.

There are two types of functions in SQL Server; they are:

1. System defined function
2. User defined function

User defined functions are three types in SQL Server. They are scalar, inline table-valued and multiple-statement table-valued.

Creating a User-Defined Scalar Function in SQL Server

Now create a function named MultiplyofTwoNumber with the two parameters number1 and number2 returning one parameter named result. Both parameters have the same type, int. The function looks as in the following:

Create FUNCTION [dbo].[MultiplyofTwoNumber]  
(  
       @Number1 int,  
       @Number2 int  
)  
RETURNS int  
AS  
BEGIN  
       -- Declare the return variable here  
       DECLARE @Result int  
       SELECT @Result = @Number1 * @Number2;  
       -- Return the result of the function  
       RETURN @Result  
END

Creating a Stored Procedure in SQL Server

A function can be called in a select statement as well as in a stored procedure. Since a function call would return a value we need to store the return value in a variable. Now creating a stored procedure which calls a function named MultiplyofTwoNumber; see:

Create PROCEDURE [dbo].[callingFunction]  
(  
@FirstNumber int,  
@SecondNumber int  
)  
AS  
begin  
declare @setval int  
select dbo.[MultiplyofTwoNumber](@FirstNumber, @SecondNumber)  
end

Now, we can execute the procedure with duplicate values to check how to call a function from a stored procedure; see: 

USE [registration]  
GO  
DECLARE  @return_value int  
EXEC  @return_value = [dbo].[callingFunction]  
    @FirstNumber = 3,  
    @SecondNumber = 4 

Now press F5 to run the stored procedure. 

A function can be called using a select statement:

Select dbo.[MultiplyofTwoNumber](3, 4) as MultiplyOfNumbers  

Now press F5 to run the stored procedure.

Output 

Summary

In this article, we learned how to call a function inside a stored procedure in SQL Server. If you want to learn more about stored procedures, read this: Learn Everything About Stored Procedures In SQL Server.