Functions are reusable code snippets. We can use/re-use our functions multiple times inside a stored procedure or inside other stored procedure. A Stored Procedure is a single monolithic piece of code that embeds the TSQL piece of code. Though it is possible to invoke a SP from another SP, but that depends on the situation. A SP can also be re-used.
e.g.
create procedure mysp1 as
begin
select
FirstName=GetFirstName(t.FullName), -- user define function
LastName=GetLastName(t.FullName), -- use define function
Age = t.Age,
DOB = t.DOB,
CurrentDate = Getdate() -- system function
from tbl t;
end
In this case, we have two user define functions GetFirstName and GetLastName and one system define function Getdate(). These functions can also be use in other stored procedure like
create procedure mysp2 as
begin
Select X.*,Y.*
From
(select
FirstName=GetFirstName(t.FullName), -- user define function
LastName=GetLastName(t.FullName), -- use define function
Age = t.Age,
DOB = t.DOB,
CurrentDate = Getdate() -- system function
from tbl1 t) X
join (select
FirstName=GetFirstName(e.FullName), -- user define function
Salary = e.sal
from tbl2 e) Y
on X.FirstName = Y.FirstName
end
In this example the GetFirstName function is used twice.
So it is clear from the above that, we are invoking and re-using function from inside the SP. This SP will be invoked from the Application for executing the operation.
We can however, invoke SP from inside
MultiStatement Table Valued Function as written in this (
http://www.dotnetfunda.com/articles/show/3325/invoke-a-stored-procedure-from-multistatement-table-valued-function-st ) article by us.
Hope this answer your question. Please feel free to ask us if you need more clarity on the same.
--
Thanks & Regards,
RNA Team
Chamu, if this helps please login to Mark As Answer. | Alert Moderator