SQL Server Functions VS Stored Procedures: What’s the difference?

Two of the most helpful objects you can create in Microsoft SQL Server are user-defined functions and stored procedures.

If you are just starting out with SQL Server, you might wonder “What’s the difference?“. In this very brief tutorial, we’ll discuss the difference between SQL Server functions and stored procedures and discuss when you should choose one over the other.

We’ll discuss these topics:

1. A summary of the difference between user defined functions and stored procedures in SQL Server
2. Different kinds of user defined functions
3. Stored procedures in SQL Server
4. Links

Everything discussed in this tutorial can be found in the following FREE Ebooks:

FREE Ebook on SQL Server User Defined Functions

FREE Ebook on SQL Server Stored Procedures

You will definitely add value to yourself through the knowledge gained from these books. Download them today!

Let’s get into it.

1. A summary of the difference between user defined functions and stored procedures in SQL Server

User defined functions can be called from within a query or manipulation statement. Depending on the type of function, it will return either a single, scalar value or a result set back to the caller.

Stored procedures introduce programming logic into your database, allowing you to create complex code for a specific task and save that code for later execution. Stored procedures cannot be called from within a query (nor are they meant to).

If you come from a programming background, you may think that user defined functions may be similar to functions in a traditional programming language like C++ or C#. Actually, stored procedures are more like functions in a traditional programming language. They are meant to store complex code that can be executed repeatedly using a simple call.

Let’s create some data to work through the examples of this tutorial. We’ll create three tables: Customers, Products and Orders

CREATE TABLE Customers(
CustID int IDENTITY(50,5) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NULL
)

CREATE TABLE Products(
ProductID int IDENTITY(20,2) NOT NULL,
ProductName varchar(20) NULL,
Price decimal(5, 2) NULL
)

CREATE TABLE Orders(
OrderID int IDENTITY(100,10) NOT NULL,
CustID int NULL,
ProdID int NULL,
Qty tinyint NULL,
Orderdate datetime NULL
)

INSERT Customers (FirstName, LastName) 
VALUES 
('Joshua', 'Porter'),
('Andrew', 'Bluefield'),
('Jack', 'Donovan'),
('Cindy', 'Thatcher'),
('Gordon', 'Acres'),
('Gretchen', 'Hamilton')

INSERT Products (ProductName, Price) 
VALUES 
('Large Bench', 198.00),
('Small Bench', 169.40),
('Coffee Table', 220.00),
('Side Tables', 265.20),
('Coat Rack', 45.00)

INSERT Orders (CustID, ProdID, Qty, Orderdate) 
VALUES 
(55, 22, 1, '2021-06-01'),
(60, 28, 2, '2021-06-06'),
(75, 26, 1, '2021-06-13'),
(50, 20, 1, '2021-07-01'),
(55, 28, 1, '2021-07-06'),
(65, 24, 1, '2021-07-14'),
(55, 26, 1, '2021-07-18'),
(50, 26, 1, '2021-07-24'),
(70, 24, 1, '2021-08-06'),
(70, 26, 1, '2021-08-06'),
(70, 22, 3, '2021-09-01')

2. Different kinds of user defined functions

There are three different kinds of user defined functions:

1. Scalar Valued Functions – Returns a single, scalar value to the caller.
2. Inline Table Valued Functions – Returns a result set to the caller.
3. Multi Statement Table Valued Functions – Also returns a result set to the caller.

All of these functions can be called within a SELECT statement.

Example of a Scalar function

You could call a Scalar function in the column list of a SELECT statement, or on one side of a WHERE clause, for example. Here is a Scalar function called GetLatestSoldProduct that is meant to return the ID of the product that we most recently sold:

CREATE OR ALTER FUNCTION dbo.GetLatestSoldProduct()
RETURNS INT
AS
BEGIN
DECLARE @prodID INT
SELECT TOP 1 @prodID = ProdID FROM Orders ORDER BY Orderdate desc
RETURN @prodID
END
GO

You could call this function on one side of a WHERE clause, like in the following query that gathers details from the Products table about this latest item we sold:

Learn more about Scalar Valued Functions.

Examples of Inline Table Valued Functions and Multi Statement Table Valued Functions

Inline functions and Multi Statement function return a result set, and can therefore be called anywhere a result set is expected, such as in a FROM clause or a JOIN clause.

Here is an example of an Inline function that generates a result set, telling us how a particular product ranks among all our products in terms of quantity sold to date:

CREATE OR ALTER FUNCTION dbo.ProductSalesRank(@theProduct INT)
RETURNS TABLE
AS
RETURN
WITH SalesCTE
AS
(
SELECT ProdID, SUM(Qty) as TotalQuantitySold, RANK() OVER (ORDER BY SUM(Qty) DESC) AS SalesRank
FROM Orders
GROUP BY ProdID
)
SELECT * FROM SalesCTE WHERE ProdID = @theProduct
GO

Here is what the result set would look like if we’re curious how our Large Bench (Product # 20) ranks overall:

Looks like our Large Bench is the 5th highest selling item!

Learn more about Inline Table Valued Functions

Multi statement functions are very similar. They also return a result set to the caller, and can be called in places where a result set is expected. The difference between Multi statement functions and Inline functions is that the result set returned to the user is basically the content of a table variable that is defined and populated within the definition of the function.

Here is an example of Multi statement function that returns a result set of the ranking of all our products in terms of quantity sold:

CREATE OR ALTER FUNCTION dbo.AllSalesRank()
RETURNS @rankingTable TABLE
(
ProductID INT INDEX idx_ProductID CLUSTERED,
TotalQuantitySold INT,
SalesRank INT
)
AS
BEGIN
INSERT INTO @rankingTable(ProductID, TotalQuantitySold, SalesRank)
SELECT ProdID, SUM(Qty) as TotalQuantitySold, RANK() OVER (ORDER BY SUM(Qty) DESC) AS SalesRank
FROM Orders
GROUP BY ProdID
RETURN
END
GO

Here is what the result set looks like:

The nice thing about Multi statement functions is that you have complete control over the table variable. You can define particular data types if you want, or constraints, or indexes, whatever you want!

Learn more about Multi Statement Table Valued Functions.

3. Stored procedures in SQL Server

As stated in the introduction, stored procedures are meant to store complex code that can be called very easily using a simple command. Stored procedures are most similar to functions or methods in a traditional programming language like C++ or C#.

Stored procedures can do basically whatever you want. They can accept input parameters as well as return values to the caller as output parameters.

Stored procedures can generate a result set, but you cannot call them in places where a result set is expected.

Here is a simple example of a stored procedure that we’ll use to insert a new product into our Products table:

CREATE PROCEDURE InsertNewProduct (@productName VARCHAR(20), @price DECIMAL(5,2))
AS
INSERT INTO Products(ProductName, Price)
VALUES (@productName, @price)

The way we call a stored procedure is through the EXECUTE statement, like so:

EXECUTE InsertNewProduct 'Bookshelf', 79.97

(We don’t need to provide a value for the ProductID column because that column uses the IDENTITY property)

This makes the job of inserting a new product much easier. Instead of running a full INSERT statement when we want to add a new product, we just execute the InsertNewProduct procedure and pass to it the name and price of the new product. The procedure takes care of running the INSERT statement into the table!

Once again, this is a very simple example. I’ve seen procedures that do some extremely complex work. But, the benefit is that all we need to do to run that “extremely complex work” is just run a simple EXECUTE statement!

Learn more about stored procedures: SQL Server Stored Procedures: The Ultimate Guide for Beginners

Let’s do some cleanup to remove the objects we created in this tutorial (the DROP IF EXISTS syntax is very useful):

DROP PROCEDURE IF EXISTS InsertNewProduct
DROP FUNCTION IF EXISTS dbo.GetLatestSoldProduct
DROP FUNCTION IF EXISTS dbo.AllSalesRank
DROP FUNCTION IF EXISTS dbo.ProductSalesRank

DROP TABLE IF EXISTS Products
DROP TABLE IF EXISTS Customers
DROP TABLE IF EXISTS Orders

4. Links

Everything discussed in this tutorial can also be found in the following FREE Ebooks!:

FREE Ebook on SQL Server User Defined Functions!

FREE Ebook on SQL Server Stored Procedures!

These FREE Ebooks contain absolutely everything you need to know about all the different User Defined Functions and Stored Procedures in Microsoft SQL Server. They will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download them today!

Next Steps:

Leave a comment if you found this tutorial helpful!

You can read up on absolutely everything you need to know about User Defined functions in the following tutorial:

User Defined Functions in SQL Server: A Complete Guide

And you can read up on absolutely everything you need to know about stored procedures here:

SQL Server Stored Procedures: The Ultimate Guide for Beginners

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!