Stored Procedure VS User-Defined Functions

 
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. This can be used in JOINs with other tables. Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

Here's the full list of Differences:


1. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
2. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
3. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
4. Unlike Stored Procedure DML operations, like INSERT/UPDATE/DELETE, are not allowed in UDF.
   
   
5. A stored procedure can have both input and output parameters whereas UDF can only have input parameters.
   
   UDF can be directly selected from, whereas a stored procedure requires one to insert the results into a tempory table or table variabl
   
   
6. Stored procedures different than multi-line TVF is that the optimizer can use statistics to create a better query plan for a stored procedure; however, no statistics are maintained on Multi-line UDFs, so the optimizer has to guess at the number of rows, which can lead to a sub-optimal plan.
7. Stored procs can create a table but can’t return table.
   Functions can create, update and delete the table variable. It can return a table
8. Stored Procedures can affect the state of the database by using insert, delete, update and create operations.
   Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations operations on the database.
9. Stored procedures are stored in database in the compiled form.
   Function are parsed and conpiled at runtime only.
10. Stored procs can be called independently using exec keyword. Stored procedure cannot be used in the select/where/having clause.
    Function are called from select/where/having clause. Even we can join two functions.
11. Stored procedure allows getdate () or other non-deterministic functions can be allowed.
    Function won’t allow the non-deterministic functions like getdate().
12. In Stored procedures we can use transaction statements. We can’t use in functions.
13. The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records.
    The function won’t allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It 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.
14. Temporary tables (derived) can be created in stored procedures.
    It is not possible in case of functions.
15. when sql statements encounters an error, T-SQL will ignore the error in a SPROC and proceed to the next statement in the remaining code.
    In case of functions, T-SQL will stop execution of next statements.