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
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.