Difference between Functions and Stored Procedures
Stored procedures have been available in SQL Server for much longer than user-defined functions. Both have similarities as well as differences. They save time and effort by providing re-usability. Both stored procedures and user defined functions can accept a maximum of 2100 parameters. Lets have a look at few important differences:
Stored procedures have been available in SQL Server for much longer than user-defined functions. Both have similarities as well as differences. They save time and effort by providing re-usability. Both stored procedures and user defined functions can accept a maximum of 2100 parameters. Lets have a look at few important differences:
| Functions | Stored procedures |
|---|---|
| Functions must always return a value. | Stored procedures do not have this requirement. |
| Functions cannot alter data or objects in a server. | Stored procedures can alter data and objects in database and server. |
| You can embed functions within a SELECT statement. | Stored procedures cannot be embedded within a SELECT statement. |
| Both functions and stored procedures can accept parameters. Functions can accept input parameters but can return only a single return value. | Stored procedures can also accept OUTPUT parameters. |
| TEMP tables can’t be used in functions. | Both TEMP tables and Table variables can be used in stored procedures. |