RSS Feed

Difference between Functions and Stored Procedures

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:

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.