In this article, you learn the difference between Stored Procedure and Function. Store procedure and function both are the SQL Server object which contain set of SQL Statements to complete there task. Both are difference from each other. We will discuss all the main differences between function and store procedure.  

Difference between Function and Store Procedure



The function must return a value

Procedure return is options means procedure can return or not. Even procedure can return zero (0) or n number of records.

Functions have only input parameters

Procedure can have input as well as output parameters.

Function can be used in store procedure

Procedure can not be used in function.

Function can allow only select statement

Procedure can allow select statement as well as Insert / Update / Delete statements.

Function can be used anywhere in SQL statement

Procedure can not be used in SQL statement.

Function can be return a table that table can be used as another rowset and this can be join with another table.

Procedure can not do this.

Function do not allow try-catch block to handle exception

Procedure allow try-catch block to handle exception.

Function also do not allow transaction

Precedure allow transaction