How to call function from stored procedure in SQL Server?
In this article I'm going to explain how to call function from stored procedure in SQL Server. Here I have written a scalar function named
AddionofTwoNumbers that accepts three input parameters and one output parameter. Now I want to call this function in Stored procedure.
In this article I'm going to explain how to call function from stored procedure in SQL Server. Here I have written a scalar function named
"AddionofTwoNumbers" that accepts three input parameters and one output parameter. Now I want to call this function in Stored procedure.
There are two types of function
1) System defined function
2) User Defined function
User Defined function are three types in SQL server They are as follows
1) Inline table valued
2)Scalar
2)Multiple Statement table valued
Creating User Defined Scalar function in SQL Server
In this example Three input parameter and one output parameter. All are same parameter.
The function looks as in the following:
CREATE FUNCTION [dbo].[AddionofTwoNumbers]
(
@Number1 int,
@Number2 int,
@Number3 int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Output int
SELECT @Output = @Number1 + @Number2+ @Number3;
-- Return the result of the function
RETURN @Output
END
Creating a Stored Procedure in SQL Server
A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc or SP) are actually stored in the database data dictionary.
CREATE PROCEDURE [dbo].[callingFunctionFromStoredProcedure]
(
@Number1 int,
@Number2 int,
@Number3 int
)
AS
begin
select dbo.[AddionofTwoNumbers](@Number1, @Number2,@Number3)
end
To run stored procedure use below line
DECLARE @Outputvalue int
EXEC @Outputvalue = [dbo].[callingFunctionFromStoredProcedure] @Number1 = 30, @Number2 = 40,@Number3 = 50
The output is
120