SQL server SP vs Function

MICROSOFT SQL SERVER  

 

Hello Readers,

                In Interviews  "difference between SP vs Function" is mostly asked Question. Below answers covers most of the points , and will help you in giving Confident and well informed answer next time you encounter this Question.

similarities : - Both are database Objects containing SQL statements.

Defination:-

Stored Procedure :- SP's are  Pre-compiled Objects , so whenever it is called the Compiled code Executes.

Function :- Function is Compiled and Executed Everytime it is Called.

SP	Function
It is Optional to return a value	Must return a value
Can take Both Input and Output parameter	Only Input param’s
Function can be called from SP	Cannot call SP
ALLOWS SELECT/INSERT / UPDATE /DELETE	Only SELECT allowed
Cannot be used in where /having part of SQL Query	Can be used in where /having part of SQL Query
Try- catch allowed	X (CANNOT BE USED)
Transaction allowed	X (CANNOT BE USED)

 

 Thanks for visiting my Blog.