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. 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:

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