Difference between Stored Procedure and Function in SQL Server
Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called.
| User Defined Function | Stored Procedure |
|---|---|
| Function must return a value. | Stored Procedure may or not return values. |
| Will allow only Select statements, it will not allow us to use DML statements. | Can have select statements as well as DML statements such as insert, update, delete and so on |
| It will allow only input parameters, doesn’t support output parameters. | It can have both input and output parameters. |
| It will not allow us to use try-catch blocks. | For exception handling we can use try catch blocks. |
| Transactions are not allowed within functions. | Can use transactions within Stored Procedures. |
| We can use only table variables, it will not allow using temporary tables. | Can use both table variables as well as temporary table in it. |
| Stored Procedures can’t be called from a function. | Stored Procedures can call functions. |
| A UDF can be used in join clause as a result set. | Procedures can’t be used in Join clause |