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:

System defined function

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:

CreateFUNCTION[dbo].[MultiplyofTwoNumber]

(

@Number1int,

@Number2int

)

RETURNSint

AS

BEGIN

-- Declare the return variable here

DECLARE@Resultint

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:

CreatePROCEDURE[dbo].[callingFunction]

(

@FirstNumberint,

@SecondNumberint

)

AS

begin

declare@setvalint

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