Differences between Stored procedures and User defined functions

Difference 1:

• Stored procedure used for perform specific tasks
• Normally functions will be used for computing value

Difference 2: 

• Stored procedures may or may not return values
•  But function should return value

Difference 3:

• Stored procedure cannot be used in the select/where/having clause
• But function can be called from select/where/having clause 

Difference 4:

• Stored procedure can run independently. It can be executed using EXECUTE or EXEC command

• But function cannot run independently, It has to be the part of the SQL statement

Difference 5:

• Temporary table (derived) cannot be created on function
• But it can be created in stored procedures 

Difference 6:

• From sql server 2005 onwards, TRY CATCH statements can be used in the stored procedures

• But it cannot be used in the function. But we can use raise error function

Difference 7:

• Stored procedure can call the user defined functions
• But the function cannot call the stored procedures

The function cannot call the stored procedures like procedures. There are many types of stored procedures in sql server.

• System Stored procedure
• User defined Stored procedure
• NET CLR stored procedure
• Extended stored procedure

Except extended stored procedures no one can call the user defined functions

Difference 8:

• Stored procedures can have input and output parameters
• But the function can have only input parameters

Difference 9:

• Stored procedures can have select and all DML operations
• But the function can do only select operation

Functions will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions

Difference 10:

• Function cannot have the transaction statements.

The transaction statement cannot be used in the function. Normally we won't do any DML operations in the function.

• Stored procedure can use transaction statements