SQL Server - Differences between a Function and a Stored Procedure

Before getting into the differences between a Function and Stored Procedure, let's know what do the both mean.

Function - A set of SQL statements that return a specific result (scalar value or a table) by accepting one or more input parameters.

Stored Procedure - A set of SQL Statements that run as a single unit to get a particular result set on either accepting some input parameters or output parameters or nothing.

Both of the above sound similar in definitions but there exist some differences because of which they both exist in the product. Let's see the main differences between them.

Function	Stored Procedure
1) Functions require at least one input parameter	1) Stored Procedure doesn't need an input parameter compulsorily
2) Accepts only input parameters	2) Accepts Input Paramters, Output Paramters or nothing for execution
3) Only SELECT statement is allowed	3) All DDL and DML statements are allowed
4) Table Valued Functions can be used in Joins	4) Cannot be used in Joins and cannot be used in SELECT statement
5) Exception Handling cannot be done	5) Exception Handling can be done
6) A Function should return something (a value or a table)	6) May or may not return anything (depends on the user's logic)
7) PRINT cannot be used	7) PRINT can be used
8) A function can call another function but not a Stored Procedure	8) A Stored Procedure can call another Stored Procedure as well as a Function
9) Doesn't support the usage of Transactions	9) Supports the usage of Transactions
10) A function gets compiled every time it is executed	10) A Stored Procedure gets compiled only for the first execution after it is created

From all the above differences, the main difference that should be discussed separately is the last difference. A Stored Procedure gets compiled only for the first time of execution after its creation while a Function gets compiled every time it is executed. For Stored Procedures, there exists a concept called EXECUTION PLAN CACHING. This means, when a stored procedure is created and executed for the first time, the execution plan which is created gets stored in the Procedure Cache. If the procedure is executed again Query Optimizer doesn't create a new execution plan, it fetches the existing one from the cache. This makes the work of Query Optimizer easier and consumes lesser time. But in the case of function, execution plan is created for each time Function is called.

One more major difference exists between Function and Stored Procedure. To understand this, let's have a small practical example.

Create a Stored Procedure by running the following code:

USE AdventureWorks2012

GO

CREATE PROCEDURE TEST_EXEC_PLAN

AS

BEGIN

   SELECT * FROM Production.Product

END

Create a Function with same logic as the above Stored Procedure:

CREATE FUNCTION dbo.FN1 (@PARAM1 VARCHAR(100))

RETURNS TABLE

AS

RETURN (SELECT * FROM Production.Product)

Execute the above both with the following statements,

EXEC TEST_EXEC_PLAN

GO

SELECT * FROM dbo.FN1('')

Same result is obtained for both the statements. Now remove a column from the base table.

ALTER TABLE Production.Product DROP COLUMN SafetyStockLevel

Now execute the Function and Stored Procedure again, you will find that Stored Procedure gets executed while the Function returns the following error

Msg 4502, Level 16, State 1, Line 1

View or function 'dbo.FN1' has more column names specified than columns defined.

This is because all the User Defined Functions are deterministic (returns the same output every time for a same input). They are marked as Non-Deterministic by SQL Server only when they call any non-deterministic functions inside their body. In this case, the function when created, should return 'n' number of columns, but after deletion of a column table has (n-1) columns which is not the criteria of the function definition. This can be considered as an advantage of Stored Procedure over the Functions.