|
|
Stored Procedure
|
Functions
|
|
Compilation
|
Stored in database in compiled format.
Note: Compiled indicates, Execution plan will be made by sql at the
time it created and stored in DB.
|
Will compiled at run time
|
|
Return type
|
·
It can directly return only integers
·
Return type is not must
|
·
It can return any scalar or table
·
Return type is must
|
|
Multiple return values
|
It can also return more than one values (of any data type) indirectly
with the help of out parameters
|
It won’t support out parameters
|
|
DML Statements
|
Can have DML statements.
|
Cannot have DML statements.
Note: In case of multi-table valued functions it can contain DML
statements affecting Table Variables.
|
|
Execution
|
· Stored procedure can execute
function.
· Cannot be the part of Select query as a
column.
· Stored Procedures cannot be used in the SQL
statements anywhere in the WHERE/HAVING/SELECT
|
· Function cannot execute stored procedure.
· Can be the part of select query as a column.
· Functions be used in the SQL statements
anywhere in the WHERE/HAVING/SELECT
|
|
Exception handling
|
Can have Try….Catch
|
Cannot have Try…Catch
|
Sometimes we face a question, why we can’t execute stored procedure inside a function?
Answer:
-
Stored Procedure may contain DML statements.
-
Function can’t contain DML statements.
So executing Function inside stored procedure will never break rule 1.
But executing stored procedure inside function may break rule no 2.
So ultimately strict rule is made why Sql team, we can’t execute stored procedures inside function.
Below is a nice SQL Server interview question video on
Unique key vs primary keys.