top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What are differences between function and stored procedure?

+2 votes
What are differences between function and stored procedure?
posted Feb 9, 2015 by Shivaranjini

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

Procedures can not be utilized in a select statement whereas function can be embedded in a select statement. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset

answer Feb 9, 2015 by Arun Gowda
0 votes

Stored Procedure:

If we want to do certain repetitive tasks/operations over our database within the same application and database, then in this case the most useful method for this functioning is none other than Stored Procedures.

It is most oftenly called a "S-Proc"or "SP".

enter image description here

Difference between Function and Stored Procedure:

1.Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.


SP: 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.

Function: Will compiled at run time

Return type:

SP: t can directly return only integers
Return type is not must

Function: It can return any scalar or table
Return type is must

Exception handling:

Sp: Can have Try....Catch

Function: Can not have Try....Catch

answer Feb 16, 2015 by Manikandan J