Difference Between Stored Procedure And Function In SQL - Interview Question

Introduction
In this article we will learn what is the difference between Stored Procedure and SQL function. Most asked Interview question the difference between function and stored procedure.

Previous Updates
In previous articles we have learnt Inner Join using lambda expression. .Arrange Distinct Elements Of A List In Ascending Order Using LINQ 

To increase performance of our database, we need to concern with several aspects during database design. One of the most useful operations for performing those operations as well as maintaining efficiency is as follows:

Why Stored Procedure

Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called which makes execution faster. Instead of retrieving data from sql queries we used Stored procedures or proc.

Difference between StoredProcedure and Function

• Procedure can return zero, single or multiple values whereas Function must return a value 

• Procedures can have input/output parameters whereas Function only have input parameters. 

• Procedures can not be called from function whereas Functions can be called from Procedure.

• Procedure allows DML (Insert/Update/Delete) and also SELECT statement whereas Function only allow Select statement.

• Procedure can not be used with Where/ having sql conditional statement whereas function can be used with conditional statements.

• Procedure can have transaction whereas Function can't.

• Procedure can have try catch for Exception-Handeling whereas Function can't.