Stored Procedures:
1 Stored Procedure may or not
return values.
2.
Can have select statements as
well as DML statements such as insert, update, delete and so on
3.
It can have both input and
output parameters.
4.
For exception handling we can
use try catch blocks
5.
Can use transactions within
Stored Procedures
6.
Can use both table variables as
well as temporary table in it
7.
Stored Procedures can call
functions
8.
Procedures can't be called from
Select/Where/Having and so on statements. Execute/Exec statement can be used to
call/execute Stored Procedure
9.
Procedures can't be used in Join
clause
User Defined Functions
1.
Function must return a value.
2.
Will allow only Select
statements, it will not allow us to use DML statements.
3.
It will allow only input
parameters, doesn't support output parameters.
4.
It will not allow us to use
try-catch blocks
5.
Transactions are not allowed
within functions
6.
We can use only table variables,
it will not allow using temporary tables
7.
Stored Procedures can't be
called from a function
8.
Functions can be called from a
select statement
9.
A UDF can be used in join clause
as a result set.
