| User Defined Functions | Stored Procedure |
|---|---|
| Functions must return a value | Stored Procedure may or may not return values |
| It will allow only SELECT statements, DML statements are not allowed here. | In SP, you can use SELECT or DML statements like INSERT, UPDATE, DELETE |
| Only Input parameters are allowed, output parameters are not allowed | SP can both Input as well as Output parametes |
| Transactions are not allowed in Functions | You can work with Transactions while working with stored procedures |
| Functions do not allow to use TRY…CATCH block | Can use TRY….CATCH block in SP for error handling |
| Here you can use table variables but not temporary tables. | In SP you can work with both table variables and temporary tables |
| Functions can be called from Stored Procedure | They cannot be called from a function |
| They can be called from SELECT statement. Like : SELECT dbo.<function-name> from <table-name> | They cannot be called from SELECT/WHERE or HAVING statements. EXEC / EXECUTE statements can be used to call a Stored Procedure |
| A User defined Function can be used in JOIN Clause as a result set | They cannot be used with JOIN clause |
TIP : Function that return tables can be treated as a rowset and can be used in JOIN statement.