In this article we will see how can we call a Function inside a Stored Procedure. You can read about Functions and Stored Procedures.

First thing to Note that we can call a Function from a Stored Procedure but vice-versa is not true.

Why Stored Procedure cannot be called from function in SQL Server

Functions have only read-only access to the database, they cannot change or modify data. Whearas Stored Procedure is able to do this task. It can update, delete, insert data into the database. So You can use Functions within Stored Procedure. But using Stored Procedure inside Functions gives side effects. It cannot alter the state of database.

Let us Create a Function and try to call it from a Procedure.
Table Taken – Student

CREATE function func_name
(
-- Declaring the input parameters
@fname varchar(10),
@lname varchar(10)
)
-- Declaring data type of ouput parameter with returns clause
RETURNS varchar (20)
as
begin
return (select @fname+' ' +@lname);
end

What this function is doing, it is concating the first name and last name.

Let us execute this function.
select dbo.func_name(fname,lname) as name, grade from student

OUTPUT





Now let us Create a STORED PROCEDURE and call this function in the procedure.
CREATE PROCEDURE sp_search
(
-- Declaring input parameters
@roll int
)
AS
BEGIN
select dbo.func_name(fname, lname) as name from student where @roll = roll
END

You can see above that inside the procedure body, we are calling our function which return the full name of the student based on the input parameter ‘roll’ (roll number of the student).

Let us Execute the above Procedure to see if it gives the desired output.
EXEC sp_search 2

OUTPUT

You can see that the function is called successfully from the Procedure. Thanks for Reading… !!!

You might be interested in our other Articles
– How to Create a Stored Procedure? Click Here
– How many types of Functions are there in SQL SERVER ? Click Here
– How to create Stored Procedures with Default Values. Click Here