Scalar Valued Function: The Ultimate Guide for Beginners

User defined functions are very common objects you will see and use when querying and developing SQL Server databases. It is very important that you know what they are and how they work.

In this first lesson on SQL Server user defined functions, we will discuss Scalar Valued Functions.

If you need a quick rundown of all the user defined functions available to us in Microsoft SQL Server, check out the full beginner-friendly introduction here:

SQL Server User Defined Functions: A Complete Guide

In this tutorial, we will cover these topics:

1. What is a Scalar Valued Function in SQL Server?
2. The syntax of a Scalar Valued Function.
3. Examples of Scalar Valued Functions.
4. What are the limitations of a Scalar Valued Function?
5. Tips, tricks, and links.

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

FREE Ebook on SQL Server User Defined Functions!

This FREE Ebook contains everything you need to know about all the different User Defined Functions in Microsoft SQL Server. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

Let’s get started!

1. What is a Scalar Valued Function in SQL Server?

A Scalar Valued Function is a function that returns a single value back to the caller. A Scalar Valued Function can be used anywhere a single value is expected. You can pass in one or more parameters to a Scalar Valued Function and do work based on those parameters. 

A scalar valued function can be used anywhere a single value is expected. This means, for example, you can call a Scalar Valued Function in a SELECT list, or on one side of the ‘=‘ operator in a WHERE clause.

We’ll look at some examples soon, but first, let’s understand the syntax of a scalar valued function.

2. The syntax of a Scalar Valued Function.

We need to understand the syntax for creating a scalar valued function. It follows this layout:

CREATE FUNCTION functionName(<optional parameter list>)
RETURNS <data type of return value>
AS
BEGIN
<function body>
RETURN <value to return>
END

There are a few things to point out here, starting from the top.

1) The <optional parameter list>

You aren’t required to pass a value into the function. There are certainly scenarios where you will have a static function that does the same work every time, and doesn’t need parameters to do it. But most of the time, your function will be dynamic via parameters.

2) The RETURNS clause where we outline the <data type of the return value>

This is an important part of Scalar Valued Functions. This is where you simply outline the data type of the value you plan on returning from the function.

3) The body of the function is outlined with a BEGIN……END block.

This is one of those things you will simply need to remember. Scalar Valued Functions need to have this block surrounding the body.

4) RETURN <value to return>

This is the final part of the function where you return the value that you want to return from the function. Note that all functions need to have a RETURN clause. This is true for not only Scalar Valued Functions, but also Inline Table Valued Functions and Multi Statement Table Valued Functions, of which we have separate discussions!

5) The <function body>

You can do many things in the body of a function. For example, you can run more than one SELECT statement if you need to, or do math, or call Stored procedures or other functions, just to name a few. At the end of the day, all you need to remember is that you need to return a single value.

Without further ado, let’s look at some examples of scalar valued functions.

3. Examples of Scalar Valued Functions.

Take a look at the following Products and Orders tables:

Here are the CREATE TABLE and INSERT statements for these tables if you want to create them in your own environment and follow along:

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 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')

Take a look at the following query that summarizes how much money we have made from each product:

We use the handy GROUP BY clause to give us a breakdown for each product. It looks like Product # 26 is presently our best selling item, bringing in a whopping $1,060.80 in sales.

But this query gives us a breakdown for all products. What if we only wanted to know how much money we’ve made from one specific product?

Well, we can alter the query slightly to give us a breakdown for a very specific ProductID value. For example, let’s say we want the summary for Product # 24:

That was easy enough. We just added a simple WHERE clause to filter our results for a specific ProductID value. The result set shows a breakdown for product # 24, but we could easily plug in a different ID to give us the breakdown for a different product if we wanted to.

We can make this query part of a scalar valued function and run it repeatedly, very easily

Folks, it would be a pain to have to write this query from scratch every time we wanted to get a breakdown of how much money we’ve made from a product.

Wouldn’t it be nice if we could somehow save this query in the database?

Through the use of a scalar valued function, we can!

Here is what the function would look like:

CREATE FUNCTION getProdSum(@prodID as INT)
RETURNS DECIMAL(6,2)
AS
BEGIN
DECLARE @productSum DECIMAL(6,2)
SELECT @productSum = SUM(O.Qty*P.Price)
FROM Orders as O
INNER JOIN Products as P
ON O.ProdID = P.ProductID
WHERE P.ProductID = @prodID
GROUP BY O.ProdID

RETURN @productSum
END
GO

Notice we pass in a prodID. The function will calculate how much money we have made from the passed-in product, write the calculation to a variable (@productSum), then return that value to the caller.

Pretty cool!

How to call a Scalar Valued Function:

To call this function, you need to make sure you do two things:

1. Call it using the schema two-part identifier.
2. Use parentheses for the parameters.

For example, we can call our new getProdSum function like this:

Since we didn’t specify a schema in the definition of the function, it defaulted to being in the ‘dbo‘ schema.

If you want your function in a different schema, you need to outline it in the definition. For example, if we wanted our function to be in a schema called ProductObjects, we would have needed to write the name of the function as CREATE FUNCTION ProductObjects.getProdSum, basically like so:

Then, of course, when we call that function, we would say ProductObjects.getProdSum(24), for example.

And again, you need to use parentheses when calling the function, even if the function doesn’t have any parameters.

An example of a Scalar Valued Function with no parameters

Let’s write a function that returns the ProductID of the product that has made us the most money:

CREATE FUNCTION getHighestSeller()
RETURNS INT
AS
BEGIN
DECLARE @highestSeller INT
SELECT TOP 1 @highestSeller = O.ProdID
FROM Orders as O
INNER JOIN Products as P
ON O.ProdID = P.ProductID 
GROUP BY O.ProdID
ORDER BY SUM(P.Price * O.Qty) DESC

RETURN @highestSeller
END
GO

Here’s the call:

So again, even though this function doesn’t take any parameters, we still need to use parentheses when calling the function. The parentheses will simply be empty.

If you think about it, we really don’t need/want to feed any input to a function like this. Right now, the highest selling product is # 26. But maybe one day this product will be overtaken by something else. If that happens, the ID of that product will be returned.

Using a Scalar Valued Function in a WHERE clause

Remember what I said earlier: A Scalar Valued Function can be used anywhere a single value is expected

We could use our new ‘getHighestSeller‘ function on one side of an ‘=‘ operator, for example. If we wanted to get all the details of that highest selling product, we could write a simple query like this:

Let’s do some function Inception and use both of our functions together:

In the SELECT list, we use the output of the getHighestSeller function as input to the getProdSum function. The getProdSum function takes an INT ProductID value as input which is exactly what getHighestSeller returns! 

Then to make sure we get details for only that one product, we call getHighestSeller again in the WHERE clause.

(A more efficient way to do this would’ve been to call getHighestSeller once and put it’s return value into a variable, then use that variable as the parameter for getProdSum and in the WHERE clause)

Pretty cool, right?

Functions help us write less code.

Lastly, you should know that Scalar valued functions help us write less code. In the case of the getProdSum function, instead of needing to remember that complicated query every time we want to know how much fresh cash we have made from a product, we simply need to remember the name of our function and the ID of the product. That’s it!

If you find yourself writing the same (or a very similar) SELECT statement over and over again, you could think about writing it as a function so that you don’t need to repeat the work all the time.

4. What are the limitations of a Scalar Valued Function?

I said earlier that you can do many things in the function body of a Scalar Valued Functions, but the possibilities are not limitless.

Here is a list of things you cannot do in a Scalar Valued Function (or any other user defined function, for that matter):

• You cannot use error handling code in your function – You cannot use TRY…CATCH blocks, for example.
• No temporary tables – You can’t write table results to a temp table within a function.
• You cannot use dynamic SQL – You can’t write a command as a string literal, then call ‘execute’ on that string literal. For example, we cannot say execute ‘SELECT ProductName FROM Products’ from within a function. 
• No Data Definition Language inside a function –  You can’t use CREATE, ALTER, or DROP statements. You couldn’t make a new table from within a function if you wanted to, for example.
• You cannot modify data from within a function – You can’t issue INSERT, UPDATE, or DELETE statements. The only exception to this rule is if you were inserting, updating, or deleting data in a table variable.

5. Tips, tricks and links. 

Here is a list of tips and tricks you should know when working with Scalar Valued Functions:

Tip # 1: You can change a function by using the ALTER FUNCTION command

If you need to change your Scalar Valued Function, you can use the ALTER FUNCTION statement followed by the function definition like so:

If you need to alter a function, you cannot just change the one part of the function. You need to rewrite the entire definition of the function.

Tip # 2: You can access the function from the Object Explorer

If you want to quickly see the definition of a function (perhaps to alter it), you can use the Object Explorer. In the Object Explorer, expand the tree for your database, then expand Programmability | Functions | Scalar-valued Functions. This will show the list of scalar valued functions in your database. You can then right-click on the function you want to edit and choose Modify, as shown here:

This will open the definition of the function in a new query window. The function will be ready for you to ALTER, as pointed out here: 

If you want to easily see the parameters for a function, you can also do that in the Object Explorer. Just expand the tree for the function, then expand the Parameters folder:

Tip # 3: Use the DROP FUNCTION statement to delete a function

To delete a function, you would run the following statement:

DROP FUNCTION <schemaName>.functionName

The <schemaName> is optional. You can leave it off and SQL will know where to delete it from (unless you have two functions that are named exactly the same, but in different schemas). Notice there is no need to specify the function definition, just the DROP FUNCTION statement followed by the name of the function. Here’s an example:

Links

There is a very helpful book out there called Clean Code that discusses the importance of non-repeating code.

Remember, as a developer, you should always try to limit the amount of code you repeat. If you find yourself repeating a certain lengthy, complicated block of code, you should strongly consider writing it as a function instead, and then just call the function every time you need that work performed.

Clean Code discusses the importance of non-repeating code, as well as many other best practices when it comes to programming. You should get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

Don’t forget to download your FREE Ebook:

FREE Ebook on SQL Server User Defined Functions!

This FREE Ebook contains everything you need to know about all the different User Defined Functions in Microsoft SQL Server. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

As a database professional, it is important you know all about the other user defined functions you can use in Microsoft SQL Server.

Be sure to check out my tutorials on the other two user defined function types, Inline Table Valued Functions and Multi Statement Table Valued Functions

If you need a rundown about user defined functions in general, take a look at my beginner-friendly introduction here:

SQL Server User Defined Functions: A Beginners Guide

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, leave a comment. Or better yet, send me an email!