Calling a Function From a Stored Procedure in SQL Server 2012

In this article, we will see how to call a function from a stored procedure in SQL Server 2012.

In this article, we will see how to call a function from a stored procedure in SQL Server 2012. 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 2012. The example is developed in SQL Server 2012 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:

  1. Create FUNCTION [dbo].[MultiplyofTwoNumber]  
  2. (  
  3.        @Number1 int,  
  4.        @Number2 int  
  5. )  
  6. RETURNS int  
  7. AS  
  8. BEGIN  
  9.        -- Declare the return variable here  
  10.        DECLARE @Result int  
  11.        SELECT @Result = @Number1 * @Number2;  
  12.        -- Return the result of the function  
  13.        RETURN @Result  
  14. 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:

  1. Create PROCEDURE [dbo].[callingFunction]  
  2. (  
  3. @FirstNumber int,  
  4. @SecondNumber int  
  5. )  
  6. AS  
  7. begin  
  8. declare @setval int  
  9. select dbo.[MultiplyofTwoNumber](@FirstNumber, @SecondNumber)  
  10. end  
Now, we can execute the procedure with duplicate values to check how to call a function from a stored procedure; see:
  1. USE [registration]  
  2. GO  
  3. DECLARE  @return_value int  
  4. EXEC  @return_value = [dbo].[callingFunction]  
  5.     @FirstNumber = 3,  
  6.     @SecondNumber = 4  
Now press F5 to run the stored procedure. 

Execute-Stored-Procedure-in-sqlserver.jpg 

 

A function can be called using a select statement:

  1. Select dbo.[MultiplyofTwoNumber](3, 4) as MultiplyOfNumbers  
Now press F5 to run the stored procedure.

Output 

Execute-Stored-Procedure-with-select-in-sqlserver.jpg
 
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.