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:

stored-procedure-in-sql-server-min.png

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  

function-example-in-sql-server-min.png

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

SELECT * FROM Sales.ufn_SalesByStore (602); 

calling-function-example-in-sql-server-min.png

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

STORE PROCEDURE FUNCTION (USER DEFINED FUNCTION)
Procedure can return 0, single or multiple values Function can return only single value
Procedure can have input, output parameters Function can have only input parameters
Procedure cannot be called from a function Functions can be called from procedure
Procedure allows select as well as DML statement in it Function allows only select statement in it
Exception can be handled by try-catch block in a procedure Try-catch block cannot be used in a function
We can go for transaction management in procedure We can not go for transaction management in function
Stored Procedure cannot be utilized in a select statement Function can be embedded in a select statement
Stored Procedure can affect the state of database means it can perform CRUD operation on database Function can not affect the state of database means it can not perform CRUD operation on database
Stored Procedure can use temporary tables Function can not use temporary tables
Stored Procedure can alter the server environment parameters Function can not alter the environment parameters
Stored Procedure can use when we want instead is to group a possibly- complex set of SQL statements Function can use when we want to compute and return a value for use in other SQL statements

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