Scalar Function: How to Create, Call, Modify & Remove Scalar Functions?

What are Scalar Functions?

SQL is one of the most commonly used languages for database management systems. The language is primarily used to enter and fetch data from repositories. Just like other programming languages, SQL has a set of functions. 

In SQL Server there are two major types of functions to manipulate data:

• System defined functions
• User-defined functions

System-defined functions are built-in functions predefined, and their functionality cannot be changed. On the other hand, user-defined functions are the ones that can be designed to perform a custom task as per our needs.

SQL Server provides three types of user-defined functions

• Simple table-valued function
• Scalar table-valued function
• Multi-statement table-valued function

In this article, we will talk about the scalar value function (user-defined functions) and learn how to create it.

A scalar value function in SQL takes more than one parameter and returns a single value of any data type.  To create a scalar value function, the “Create” statement is used. These types of functions are included in the code to simplify the code. 

For example, you may need to carry out a challenging calculation at various steps in your code. Instead of writing the code, again and again, you can simply create a scalar function and encapsulate the code in it. Now, all you need to do for carrying out that tedious calculation is to call the scalar function with a simple syntax.

 Remember – The name of a scalar value function cannot be more than 128 characters and must start with the prefix fn for convenience. 

How to Create a Scalar Function?

For creating a scalar function, you need to use the “Create function” statement as mentioned below:

1 CREATE FUNCTION [schemaName.]fnName (parameterlist)

2 RETURNS data_type AS

3 BEGIN

4   statements

5   RETURN value

6 END

Understanding the Syntax

Line 1 – CREATE FUNCTION is the command telling the SQL server to create a scalar function. Here, the schema name is optional; if not specified, SQL uses dbo by default. The schema name is followed by the function’s name, which must have parameters enclosed within the parenthesis.

Line 2 – Specifies return value’s data type in the RETURN statement.

Line 3 –  Specifies return statements to return value inside the function’s body.  

Example

The following example creates a function that calculates the total sales based on the list price, quantity, and discount:

CREATE FUNCTION sales.TotalSale(

    @quantity QTY,

    @listprice JAN(15,4),

    @disnt Jan(13, 2)

)

RETURNS JAN(15,4)

AS 

BEGIN

    RETURN @quantity * @list_price * (1 – @disnt);

END;

This function can be used later to compute the total sales of any sales order from the database.

How to Call a Scalar Function?

A scalar function can be called just like a built-in function. 

Example: The statements mentioned below clearly show how to call the TotalSale () function:

SELECT 

    sales.TotalSale(10,100,0.1) total_sale;

How to Modify a Scalar Function?

In order to modify the scalar function, the keyword ALTER is used instead of CREATE. All the other statements after that remain the same:

ALTER FUNCTION [schemaName.]fnName (parameterList)

    RETURN data_type AS

    BEGIN

        statements

        RETURN value

    END

How to Remove a Scalar Function? 

DROP FUNCTION statement is used to remove a scalar function that already exists:

Here is the syntax of the function:

DROP FUNCTION [schemaName.]fnName;

For example, to remove the TotalSale( ) function, you can use the following statement:

DROP FUNCTION TotalSale

Scalar functions are one of the most commonly used functions in SQL. These functions work like built-in functions but are actually user-defined. You can create a number of functions if a set of statements needs to be repeated in your code. All you need to do after that is call the function and pass the appropriate parameters. Here are some key takeaways from SQL Scalar functions:

• You can use scalar functions anywhere in the SQL statements.
• Scalar functions use logics like  WHILE loops and IF blocks.
• Scalar functions can call other functions.
• Scalar functions cannot access data.
• Scalar functions should have a RETURN value because they accept more than one parameter but return only a single value.

Conclusion

Creating scalar functions in your programs is a good practice in SQL. Scalar functions are one of the best and effective ways to make the code more accessible for anyone to read. We hope that the article has helped you learn about scalar functions. If you are looking forward to becoming a programmer and have comprehensive knowledge of the subject, consider taking courses from upGrad. upGrad offers the best computer science courses from leading global universities. 

If you’re interested to learn more about full-stack software development, check out upGrad & IIIT-B’s Executive PG Programme in Software Development – Specialisation in Full Stack Development which is designed for working professionals and offers 500+ hours of rigorous training, 9+ projects, and assignments, IIIT-B Alumni status, practical hands-on capstone projects & job assistance with top firms.