Difference between Stored procedure and function in SQL Server

If you have just started learning SQL, a question may arise in your mind "What is the difference between Stored procedure and function in SQL Server?", so today in this article, I will quickly list out important differences between functions and stored procedures in SQL server.

Stored Procedure in SQL server

A stored procedure in SQL Server is a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method. Procedures resemble constructs in other programming languages because they can:

• Accept input parameters and return multiple values in the form of output parameters to the calling program.

• Contain programming statements that perform operations in the database. These include calling other procedures.

• Return a status value to a calling program to indicate success or failure (and the reason for failure).

Example

For this example, I will be using AdventureWorks2012 SQL server sample database, if you don't have AdventureWorks Database, you can download and restore AdventureWork database first

Now, let's create a stored procedure, which has input paramters as LastName,FirstName and gives output as FirstName, LastName, Department based on user first and last name from HumanResources.vEmployeeDepartmentHistory table

USE AdventureWorks2012;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployeesTest2   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   

    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  
GO  

You can execute the above stored procedure, using the new query as below

EXEC HumanResources.uspGetEmployeesTest2 
@LastName = N'Ackerman', @FirstName = N'Pilar';  
GO  

Output:

Benefits of Stored procedures:

1. Reduces server/client network traffic as the commands in a procedure are executed as a single batch of code.
2. Provides stronger security.
3. Easier to maintain, as when client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.
4. Provides better performance as a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.  

Functions (User-defined functions) in SQL server

A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. In other words, Function may contain a set of statement as stored procedure but generally we create function if there is some calculations which we can do frequently.

Example:

We will again use the same database AdventureWorks2012, so let's create a function in it as below, it returns an inline table-valued function in the AdventureWorks2012 database. It returns three columns ProductID, Name and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.  

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
    FROM Production.Product AS P   
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
);  
GO  

Now, you can invoke the function, run this query:

SELECT * FROM Sales.ufn_SalesByStore (602); 

Difference between Stored procedure and Functions

Here is the list of differences:

1. Stored procedure may or may not return a value but if it returns a value it must be of int data type,but function return the values of any data type.
2. It is mandatory for Function to return a value while it is not for stored procedure.
3. A stored procedure can return multiple parameters but a function can return only one value.
4. We can't use Stored procedures in SELECT/WHERE/HAVING but We can use user-defined functions in SELECT/WHERE/HAVING statement. 
5. Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function
6. We can use the function within the Queries, but for stroed procedure's we won't able to use it in the queries.
7. Transaction management is not possible in functions but it is possible in Stored procedures.
8. We can implicitely execute the stored procedure's. But function's we can't.
9. Print function can not be called within the function but it can be called within the stored procedure.
10. Exception handling can be done in Stored procedure but not in function.
11. Stored procedure will always allow for return to zero. User defined functions, on the contrary, has values that must come-back to a predetermined point.
12. Stored Procedure can use temporary tables While functions cannot.

Difference in Tabular form

I hope above details clears all the difference betweem SP and Function.

You may also like to read:

Stored procedure in SQL Server with example

Difference between SQL and NoSQL (SQL vs NoSQL)

SQL server date format and converting it (Various examples)

SQL Comments (Comment in SQL Query)

SQL Server Management Studio (SSMS) Versions

Validate Email address in SQL Server

Uniqueidentifier in SQL Server

Check if table exists then delete it in SQL Server

Drop all tables of SQL Server database