why using functions instead of stored procedures? [Resolved]

Posted by Chamu under Sql Server on 11/9/2016 | Points: 10 | Views : 342 | Status : [Member] | Replies : 1
why using functions instead of stored procedures?




Responses

Posted by: Rajnilari2015 on: 11/10/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Login to post response