Executing SQL Scalar Functions

In this tutorial, we are going to learn about executing SQL Scalar functions. SQL Scalar function is just like a stored procedure that is written in an SQL script.

What are SQL Scalar functions?

An SQL scalar function is a user-defined function written in SQL and it returns a single value each time it is invoked. … An inlined SQL scalar function has a body with a single RETURN statement. The RETURN statement can return either a NULL value or a simple expression that does not reference a scalar full select.

There are several ways how to execute scalar functions. Most of the time this will be used using a SELECT and a SET command. We can also use an EXECUTE command like how we execute a stored procedure.

Before you proceed with this tutorial, Make sure to have an SQL Server installed on your machine. Or you can download it here.

I. Create a Scalar function

Creating a function is just a simple task. Please see the code snippet below. This code will return a sum of a number pass on the parameter.

CREATE FUNCTION dbo.f_sumNumber(
     @val1 int,
     @val2 int
 )
 RETURNS VARCHAR(200)
 AS
 BEGIN
     RETURN @val1 + @val2
 END
 GO

Once you executed this script. The function will be created under programmability » Functions » Scalar-valued Functions. See the image below.

II. Use SQL Function using Select

Now, let’s try to use the function by using the SELECT command. See the code snippet below.

SELECT dbo.f_sumNumber(5, 2)

This code will return the result from the functions we created a while ago. This is the most common way how we call a Scalar Function. See the image below.

We can also assign the result of the function to a variable like this. See the code snippet below.

DECLARE @sum int
SELECT @sum = dbo.f_sumNumber(5, 2)
PRINT @sum

III. Use SQL Function using SET

Now, let’s try it using a SET command. This is more like how we use the Select command using a variable. See the code snippet below.

DECLARE @sum int
 SET @sum = dbo.f_sumNumber(5, 2)
 PRINT @sum

The difference between SET and a SELECT is you can’t use the SET without using a variable. This will return a syntax error.

SET @sum = dbo.f_sumNumber(5, 2)

IV. Use SQL Function using EXECUTE

You might be familiar of this method, since we are using this on a SQL Stored procedure. To use execute see the code snippet below.

DECLARE @sum int
EXEC @sum = dbo.f_sumNumber 5, 2
PRINT @sum

You may also call it by specifying the value for each parameter. See the code below.

DECLARE @sum int
EXEC @sum = dbo.f_sumNumber @val1 = 5, @val2 = 2
PRINT @sum

You may execute this function multiple times like this one.

DECLARE @sum int
DECLARE @sum2 int
EXEC @sum = dbo.f_sumNumber @val1 = 5, @val2 = 2
EXEC @sum2 = dbo.f_sumNumber @val1 = 7, @val2 = 1
PRINT @sum
PRINT @sum2

If you are using a SQL that has a different user and had security access. You may call this functions using a specific Login User like this.

EXEC ('DECLARE @FinalString int; 
        EXEC @FinalString = dbo.f_sumNumber 1, 3; 
        PRINT @FinalString') AS LOGIN = 'freecode'
 GO

Summary

This is a different way of Executing SQL Scalar Functions. Which is better? Using SET and SELECT is the most easier way to do the task. While Execute is commonly used on SQL stored procedure. Hopefully, you find this article helpful and be used on your future project.

KEEP CODING!!