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.
