Functions VS Stored Procedures
1-Function must have a Return Type while there is no necessity to return a value with Store Procedures.
2-Function can be called inside Stored Procedures but Stored Procedures cannot be call inside
Function.
3-Function can be used with SQL Clauses like Where having and even in Select also.
ie
Select * from Function_Name(parameter1,parameter2)
4-Function can be used with apply operation Joins where a stored procedure can't.
example
select a.name,b.age from table 1 a join function_name(parameter1) as b
select a.name,b.age from table 1 a cross apply function_name(parameter1) as b
5-In function you cannot use exceptional Handling ie Try Catch Block but it can be done inside a Stored Procedure
6-Function allow only Select Statement whereas in a S.P we can use select,insert ,update delete means any DML commands
7 S.P can have Both Input as well as Output Parameter whereas a Function only Have Input Parameters.
8 S.P are Pre Compiled and its plan is cached and used in Subsequent operations whereas every time a function executes it's cause re compilation
9 Inside Function you cannot create temp tables whereas in S.P you can create temp tables as well as table variables.
10 Transactions cannot be used inside a function whereas in S.P it can.
Note- In order to store data of select clause inside a Function you can use table variable.
No comments:
Post a Comment