Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconFull Stack Developmentbreadcumb forward arrow iconScalar Function: How to Create, Call, Modify & Remove Scalar Functions?

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

Last updated:
26th May, 2021
Read Time
6 Mins
share image icon
In this article
Chevron in toc
View All
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.

Check out our free courses to get an edge over the competition

Ads of upGrad blog

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


4   statements

5   RETURN value


Check out upGrad’s Advanced Certification in Cloud Computing 

Explore Our Software Development Free Courses

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.  


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)

Check out upGrad’s Advanced Certification in Cyber Security





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


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:


    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



        RETURN value


upGrad’s Exclusive Software Development Webinar for you –

SAAS Business – What is So Different?

Explore our Popular Software Engineering Courses

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:


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.


Ads of upGrad blog

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. 

In-Demand Software Development Skills

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.

Read our Popular Articles related to Software Development


Rohan Vats

Blog Author
Software Engineering Manager @ upGrad. Passionate about building large scale web apps with delightful experiences. In pursuit of transforming engineers into leaders.

Frequently Asked Questions (FAQs)

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

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

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

Explore Free Courses

Suggested Blogs

8 Exciting Full Stack Coding Project Ideas & Topics For Beginners
A Full stack developer is an engineer who can design and develop an end-to-end application independently by handling all the work of coding, databases
Read More

by upGrad

19 Feb 2024

17 Interesting HTML Project Ideas & Topics For Beginners [2024]
Summary In this article, you will learn 10 Interesting HTML Project Topics. Take a glimpse below. A tribute page A survey form Technical documentati
Read More

by Rohan Vats

19 Feb 2024

How to Open json File in Excel
What is JSON? JSON (JavaScript Object Notation) is a file format that is used for storing and exchanging data in the network. It is used to send data
Read More

by Rohan Vats

19 Feb 2024

JSP vs Servlet: Difference Between JSP & Servlet [2024]
Websites are collections of static files, for example, images, graphics, and HTML files. These websites are referred to as web applications if they pr
Read More

by Rohan Vats

19 Feb 2024

Java Developer Salary in India in 2024 [For Freshers & Experienced]
Wondering what is the range of Java developer salary in India? From choosing your first programming language to writing apps for Android and several
Read More

by Rohan Vats

19 Feb 2024

Polymorphism In OOPS: What is Polymorphism [Detailed Explanation]
Polymorphism in OOPs is inseparable and an essential concept of every object-oriented programming language. An object or reference basically can take
Read More

by Rohan Vats

19 Feb 2024

Literals In Java: Types of Literals in Java [With Examples]
Summary: In this article, you will learn about Literals in Java. Literals in Java Integral Literals Floating-Point Literals Char Literals String Lit
Read More

by Rohan Vats

19 Feb 2024

Top 30 Exception Handling Interview Questions and Answers [For Freshers & Experienced]
Exception handling is a concept that is implemented in algorithms to handle possible runtime errors, which may disrupt the normal flow of a program. S
Read More

by Rohan Vats

19 Feb 2024

Top 40 MySQL Interview Questions & Answers For Beginners & Experienced [2024]
Have a Data engineering or data science interview coming up? Need to practice some of the most asked MySQL interview questions? The article compiles t
Read More

by Rohan Vats

19 Feb 2024

Schedule 1:1 free counsellingTalk to Career Expert
footer sticky close icon