In this blog I am trying to differentiates between Store Procedure and UDF User Define Function:
1. Stored procedure may or not return values.
2. It will allow DQL select statements as well as DML statements such as insert, update, delete etc.
3. Stored procedures have mutually input and output parameters.
4. We can use try and catch blocks for exception handling in stored procedures.
5. Stored procedure can use transactions inside stored procedures.
6. Stored procedure can use both table variables as well as temporary table in it.
7. Stored Procedures be able to call functions.
8. Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure.
9. Store procedures can’t be used in Join clause.
UDF User Define Function:
1. Function necessities return a value.
2. UDF will allow only Select statement; it will not allow us to use DML statements.
3. UDF will allow only input parameters, doesn’t support output parameters.
4. UDF will not allow us to use try-catch blocks.
5. UDF transactions are not allowed within functions.
6. We can use only table variables; UDF will not allow using temporary tables.
7. Stored procedures can’t be called from function.
8. Functions can be called from select statement.
9. UDF can be used in join clause as a result set.