Difference between Stored Procedure and Function

In a database system, stored procedures and functions are SQL statements that provide similar functionality. Both allow us to create bundles of SQL statements or codes that are stored on the server for future use. But they have some dissimilarities. This article describes a comparison   between SQL Function and Stored Procedure . Summary of the article:

• What is SQL Function?
• What is Stored Procedure (SP)?
• SQL Function VS Stored Procedure

What is SQL Function?
SQL function is a set of SQL statement which is written in database server or database.

What is Stored Procedure (SP)
SP is a set of SQL statement which is written in database server or database. It can handle complex operation according to input. Its input is called parameter. Any SP may have multiple parameters or not. If we write code in SP rather than client end it is faster. Because SP stay & run inside database server. Many database support SP but its code may differ according to database server. MSSQL Server, mySQL(vertion 5+), Oracle, DB2 etc support SP

SQL Function VS Stored Procedure
The difference between functions and stored procedures  are given bellow:
Function(User Defined)

1. It returns only one value
2. We can’t use transaction in function
3. Only have input parameter
4. We can’t called SP from function
5. We can’t use exception handling using Try-Catch block in function
6. We can use function in select/where/having statement

Stored Procedure (SP)

1. It returns zero, single or multiple values
2. We can use transaction in SP
3. Can have input/output parameter
4. We can called function from SP
5. We can use exception handling using Try-Catch block in SP
6. We can’t use SP in select/where/having statement

Function and stored procedure is tremendous features of DBMS. Both provides lot benefits which makes our task easy and simple. Their efficiency is high. To get better output one should clear concept on function and stored procedure (SP)