Difference between Stored Procedure and Function in sql server
| Function | Stored Procedure | |
| 1 | Return only 1 value | Return many values max:1024 |
| 2 | Only input parameters available | Input-output both kind of parameters available |
| 3 | Must have at least one input parameter | Not mandatory to pass input parameters |
| 4 | Can be used with SELECT statement | Can’t be used with SELECT statement |
| 5 | Can be called from Stored Procedure | Can’t be called from function |
| 6 | For calling function needs other SQL statement | No need any kind of other SQL statement to call, call using EXEC |
| 7 | must always return a value either a scalar value or a table. | may return a scalar value, a table value or nothing at all |
| 8 | can only read data | can be used to read and modify data |
| 9 | can be used as an inline with a select statement | cannot be used as an inline with a select statement |
| 10 | Function is compiled and executed every time it is called | Stored procedures are compiled for first time and compiled format is saved and executes compiled code whenever it is called |
| 11 | try-catch block cannot be used | Exception can be handled by try-catch block in a Procedure |
| 12 | Doesn’t allow Transaction Management | Allows Transaction Management |
| 13 | Can’t be used to change server configuration settings | can be used to change server configuration settings |
| 14 | can be used as user defined data types | Can’t be used as user defined data types |