SQL- Store Procedure Vs Function

Stored Procedure: SP is a SQL entity which helps in achieving consistent logic across application. SQL is group of SQL-transact statement complied to one single execution plan or would say a pre-compiled object compiled to first time only. SPs having a single execution plan which is point to ponder which makes it differ from function. At the time when a stored procedure is created a partial execution plan is saved in system table.
While function is compiled and executed every time it is called.

Function: Function are quite different from SPs serve purposes like a function do in programming languages. Take inputs and do some specific calculation.

Major differences listed below:
# Function can have only input parameters, SP can have both input and output parameters.
# Function: always return a value In SP it is optional.
# One input parameter is mandatory wiht Functions.
# We can call function from Stored Procedures, calling SP from function is not possible.

# Function allow only SELECT statements In SPs we can do DML statements as well i.e.  INSERT/UPDATE/DELETE.
# We can’t use transactions in Functions.
# We can use Funtions in SELECT statements.
# Exception handling i.e. try catch blocks is possible with SPs only.
# Tables return from function can be used with JOINS.

 

Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s