Difference between Stored Procedure and Function in SQL

In many Interviews this question is very frequently asked by the interviewer, that what difference between Stored Procedures and Functions .So I am explaining some differences

1.         Stored Procedures in database are in compiled Format but Functions compiled at run time.

2.         We can use DML Statements (insert, update and delete database table) in stored procedure ,but in functions we cannot use DML Statements

3.         Function can be used with in stored procedures, but stored procedures cannot be used with in Functions.

4.         Functions can execute with select statements, but stored procedures cant not used with select statements, for executing procedure exec/execute key word we have to put before Procedure name.

5.         Functions can used as the column in select Statements, Stored procedure cannot.

6.         Functions can used with WHERE and HAVING ,Stored procedure Cannot be used with Where and having 

7.         In Functions only input parameter we can use, In stored procedure both input and output parameter can use.

8.         Functions must return a value, but in case of stored procedure they may or not return value.

9.         Functions can use with join because table valued function return table with can use in join ,stored procedure can not used with a  join

10.       In Function Table variable can be used temporary table cannot used with functions, In stored procedure table variable and temporary table both can used.

11.       In function exception handling cannot be done (we cannot use try and catch with in the functions),Exception handling  can be done with in stored procedure 

Examples

1.         In Functions only input parameter we can use, In stored procedure both input and output parameter can use.

                         Both input and output parameter can used with stored procedure.

                        We can not use output parameter in function. 

2.         DML  Statements in Stored procedures and Function

                            

                          we can use DML statements in stored procedure.

 

DML statement through error when using in function.

3.         Functions can used as the column in select Statements, Stored procedure cannot.

                           Function can use with select statement.

 

Stored procedure can not use with select statement.

4.         In function exception handling cannot be done (we cannot use try and catch with in the functions),Exception handling  can be done with in stored procedure 

                          In Stored Procedure we can use try catch.

 

In Function try catch not working.

Autor: Er. Yashodhara Sharma