Calling User - Defined Function in Stored Procedure

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