Programs

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.

Learn Software Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

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.

What are functions in SQL?

Function is a special type of procedure in SQL that returns a value. Function returns a single value, returns multiple values in a list, or aggregates a set of rows into a single value. In other words, it makes a calculation on the fly, rather than storing the information in the database, so that your query can proceed rapidly. Functions are the basic set of defined predicates in SQL, which are used to compose SQL statements. Functions are categorized in to scalar and Aggregate functions. Scalar functions are the ones that return a single value from an expression.

What is SQL?

SQL stands for Structured Query Language. It is a special programming language which is used for managing data. It is used in most of the database systems. The data can be in the form of text, numbers, images and videos. SQL language is used to perform data query and data entry. SQL language is one of the most powerful languages. It is used to store data in the database. It is supported by various database systems like MySQL, Oracle, PostgreSQL, etc. These systems can be used to store large amount of data which can be used to form the database. It is the core language for database management systems. It is universal language for database systems. The database is structured by the SQL, so it is called structured query language. SQL is used for database management and to manage the database operations. It is a standard language for database query and data entry.

What is the difference between SQL and MongoDB?

SQL ( Structured Query Language ) is a programming language designed for managing data in relational database management systems . SQL is one of the most important and widely used programming language in the world. It is a standard for storing, retrieving and managing data in relational database management systems. For example, Google, Amazon, Facebook, Instagram, Twitter are using SQL to store and manage billions of user data. MongoDB is a non-relational database. It is a document based and schema-free database. Data in MongoDB is stored in the form of JSON-like documents.

Want to share this article?

Prepare for a Career of the Future

Leave a comment

Your email address will not be published. Required fields are marked *

Our Popular Software Engineering Courses

Get Free Consultation

Leave a comment

Your email address will not be published. Required fields are marked *

×
Let’s do it!
No, thanks.