What are differences between function and stored procedure?

Question

What security features are available for stored procedure?

Answer 1

Procedures can not be utilized in a select statement whereas function can be embedded in a select statement. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset

Answer 2

Stored Procedure:

If we want to do certain repetitive tasks/operations over our database within the same application and database, then in this case the most useful method for this functioning is none other than Stored Procedures.

It is most oftenly called a "S-Proc"or "SP".

Difference between Function and Stored Procedure:

1.Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

Compilation:

SP: Stored in database in compiled format.
Note: Compiled indicates, Execution plan will be made by sql at the time it created and stored in DB.

Function: Will compiled at run time

Return type:

SP: t can directly return only integers
Return type is not must

Function: It can return any scalar or table
Return type is must

Exception handling:

Sp: Can have Try....Catch

Function: Can not have Try....Catch