SQL Server Documentation

Scalar Functions

Scalar functions are Transact-SQL functions that return a single value. They can be used in various parts of a SQL statement, including the SELECT list, WHERE clause, JOIN conditions, and ORDER BY clause.

Scalar functions perform an operation on one or more arguments and return a single result. These functions are fundamental for data manipulation and retrieval, enabling complex calculations and data transformations directly within your queries.

Types of Scalar Functions

SQL Server provides a rich set of built-in scalar functions, categorized by their purpose. Some of the most common categories include:

• String Functions: For manipulating character strings (e.g., LEN, SUBSTRING, UPPER, REPLACE).
• Numeric Functions: For mathematical operations (e.g., ABS, ROUND, FLOOR, CEILING).
• Date and Time Functions: For working with date and time values (e.g., GETDATE, DATEPART, DATEDIFF, DATEADD).
• System Functions: For retrieving information about the SQL Server environment or current user (e.g., @@VERSION, USER_ID, DB_NAME).
• Conversion Functions: For converting data types (e.g., CAST, CONVERT).

Using Scalar Functions

Scalar functions can be used directly in your SQL queries. Here are a few examples:

Example 1: Using a String Function

This example demonstrates using the UPPER function to convert a customer's last name to uppercase.

SELECT
    CustomerID,
    FirstName,
    UPPER(LastName) AS LastNameUpper
FROM
    Customers
WHERE
    City = 'London';
            

Example 2: Using a Date and Time Function

This example retrieves the current date and time using GETDATE and calculates the difference in days between two order dates.

SELECT
    OrderID,
    OrderDate,
    ShipDate,
    DATEDIFF(day, OrderDate, ShipDate) AS DaysToShip
FROM
    Orders
WHERE
    DATEDIFF(day, OrderDate, GETDATE()) > 30;
            

Example 3: Using a Numeric Function

This example rounds a product price to two decimal places.

SELECT
    ProductName,
    Price,
    ROUND(Price, 2) AS RoundedPrice
FROM
    Products
WHERE
    Category = 'Electronics';
            

Creating User-Defined Scalar Functions

In addition to built-in functions, you can create your own scalar functions using Transact-SQL. This allows you to encapsulate complex logic or business rules into reusable units.

The basic syntax for creating a scalar function is:

CREATE FUNCTION dbo.MyScalarFunction
(
    @parameter1 datatype,
    @parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
    -- Declare variables
    DECLARE @result return_datatype;

    -- Function logic
    SET @result = @parameter1 + @parameter2; -- Example logic

    RETURN @result;
END;
            

Commonly Used Scalar Functions