|
|
Stored Procedures |
Function |
|
Basic Difference |
|
1 |
Stored Procedure it is optional (Procedure can return zero or n values) |
Function must return single value |
|
2 |
Stored Procedures can have input/output parameters |
Functions can have only input parameters |
|
3 |
Functions can be called from Stored Procedure |
Stored Procedures cannot be called from Function |
|
Advance Difference |
|
4 |
Exception can be handled by try-catch block in a stored procedure |
Exception try-catch block cannot be used in a function |
|
5 |
StoredProcedure allows SELECT as well as DML(INSERT/UPDATE/DELETE)
statement in it
|
Function allows only SELECT statement in it |
|
6 |
Stored Procedures cannot be utilized in a select statement |
Function can be embedded in a select statement |
|
7 |
Stored Procedures cannot be used in the SQL statements anywhere in the
WHERE/HAVING/SELECT section
|
Function can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
section
|
|
8 |
We can go for Transaction Management in Stored Procedure. |
We cannot go for Transaction Management in Function |
|
9 |
Stored Procedure can fire Triggers |
Function can not fire Triggers. |
|
10 |
Stored Procedure can create Table variable but can not return table variable |
Function can create Table variable and return table variable. |
|
11 |
Print command can be use in stored procedure |
Function can not be use print command. |
|
12 |
Store procedure can execute Dynamic SQL |
Function cant not be execute Dynamic SQL |
|
13 |
DML and DDL operation can be performed in SP |
DML and DDL operatoin can be performed in function but we can use only select statement
and Insert statement to insert row into table variable
|
|
14 |
Temporary table can be accessed in stored procedure. Temporary Table can be created in SP |
Temporary Table Can not be accessed in Function. We can not create Temporary in Function |