Difference Between Function And Procedure In SQL

Introduction

We are very familiar with this two things if we are familiar with the database thing. procedures are called from our web application or window applications and procedures are too. Both are meant to serve almost the same purpose, but there is a difference between them. 
Here in this article I will explain that with some examples.

What is a Procedure and a Function in SQL ?

Stored Procedure:


         Stored procedure is a program that is physically present inside the database and those are executed by the database engine itself ( automatically ) in response to a call from the user.A stored procedure may return anything or not.

User Defined Function:

 
        A user defined function is a block of logic that is written for performing a specific task. Those can not be executed directly by the database engine in response to the user call. But a function must return something.

There are three types of User Defined Functions as below;
  • Scalar Valued Function : Can return a scalar value like an integer, TimeStamp, string. So this function can be used as a column name in a query.
  • Inline Function : Can contain a single select statement.
  • Table Valued Function : Can contain multiple row sets, that means it can return a table with the help of multiple statements.

Difference Between Procedure and Function


Procedure :
  1. It can contain any update, delete, alter, open statements.
  2. Returns integer integer value always by default.
  3. Can have input/output parameters.
  4. Procedures can not be called from a user defined function.
  5. Exception can be handled by using try - catch.
  6. We can use transactions inside a procedures.
  7. Can not be called as an inline query ( can not be called from a select statement )
Function :
  1. A function can contain a insert, update delete statements but that function can not be called from a SQL query.
  2. Returns one value which is mandatory.
  3. Can have only input parameters on output parameters.
  4. Function can be called from a procedure.
  5. No Exception handling can be done by using try - catch block.
  6. Can not use transaction inside a function.
  7. Can be called in a SQL query ( that behaves as a column in a select statement ; only scalar valued function )

That's all for the differentiation between User Defined Functions and Stored Procedure.
Happy Coding...

No comments:

Post a Comment