Scalar functions in SQL

Scalar functions return a single scalar value whose data type is defined in the RETURNS clause.

Next we will see with examples , the statements to create, execute, invoke, modify and delete scalar functions.

CREATE FUNCTION

The CREATE FUNCTION statement allows us to create new functions in the database:

CREATE FUNCTION functionName (parameter1, parameter2...)
RETURNS [data type]
    AS
    BEGIN
        [CODE BLOCK]
        RETURN EXPRESSION
    END

In the following example we create a new function called applyDiscount that as its name suggests, allow us to pass input parameters: the discount and the product to wich we want to apply it.

CREATE FUNCTION  applyDiscount(@product VARCHAR(55), @discount int)
RETURNS VARCHAR(255)
AS
BEGIN
        DECLARE @result VARCHAR(255);
        DECLARE @productName VARCHAR(55);

        SET @productName = (SELECT product
                            FROM Orders
                            WHERE product LIKE '%'+@product+'%');

        IF @productName IS NOT NULL
            BEGIN
                DECLARE @dicountPrice float =
                    (SELECT price - @discount
                    FROM Orders
                    WHERE product = @productName);

                SET @result = 'THE PRODUCT: ' +
                 @productName +
                ' IS REDUCED TO: ' +
                CAST (@dicountPrice AS VARCHAR(50))+ ' EUROS';
            END
        ELSE
                SET @result = 'THE PRODUCT HAS NOT BEEN FOUND';
  RETURN @result
END
GO

Taking into account the following function: applyDiscount we draw the following conclusions to break down its operation:

• It receives two input parameters: @Product and @discount

• the @ProductName variable saves the exact value of the product field of the Orders table filtering by the string received in the @Product input parameter.

• If @ProductName has been filled in, we use its value to save in the variable @PriceDiscount the result of subtraction: the database price of the product minus the value received as input parameter of @discount. Later we set the variable @result with a message indicating the product and price reduced and we return the value in the RETURN

• If @ProductName has not been filled, we return in the RETURN the variable @result set with the message: NO PRODUCT FOUND

Once the new function has been created, we will be able to see it graphically by displaying it in the following subdirectory path of the SQL Server Management Studio menu:

INVOKE FUNCTION

We can execute functions directly from the SELECT statement. To do this, we just write the function name after the SELECT statement. If the function receives input parameters, we should introduce the respective value between the function parenthesis, respecting the order in wich they were declare and the datatype they can support.

SELECT dbo.applyDiscount('PLAY', 100)

We can also call them from a block of code:

DECLARE @a INT = 5;
DECLARE @b INT = 1;

IF @a > @b OR @a =@b
    BEGIN
        print dbo.applyDiscount('PLAY', 100);
    END
ELSE
    PRINT 'THE CONDITIONS ARE NOT MET'

It is also possible to set the value returned by a function in a variable:

DECLARE @a INT = 5;
DECLARE @b INT = 1;

IF @a > @b OR @a =@b
    BEGIN
        DECLARE @discount VARCHAR(255);
        SET @discount = dbo.applyDiscount('PLAY', 100);
        PRINT @discount
    END
ELSE
    PRINT 'THE CONDITIONS ARE NOT MET'

In all three cases, the execution result returns the text string: THE PRODUCT: Play Station 5 HAS BEEN REDUCED TO: 449.95 EUROS.

ALTER FUNCTION

ALTER FUNCTION defines the statement to modify user functions that have already been declared. The modifications may affect the arguments or input parameters, the type of data returned in the RETURNS or the internal logic.

For the example of modifying our applyDiscount function, let’s assume the following execution:

SELECT dbo.applyDiscount('P', 100)

Having entered only one character as the first parameter, our inner SELECT finds more than one record containing a P in the product field.

To avoid this error and force the user to be more specific when entering the name of a product, we will perform the following ALTER FUNCTION:

ALTER FUNCTION  applyDiscount(@Product VARCHAR(55), @discount int)
RETURNS VARCHAR(255)
AS
BEGIN
        DECLARE @result VARCHAR(255);
        DECLARE @productName VARCHAR(55);
        DECLARE @productQuantity INT;
        SET @productQuantity= (SELECT COUNT(*)
                               FROM Orders
                               WHERE product like '%'+@Product+'%');

        IF @productQuantity > 1
            BEGIN
                SET @result = 'YOU SHOULD BETTER SPECIFY THE NAME OF THE PRODUCT';
                RETURN @result
            END
        SET @productName = (SELECT product
                            FROM Orders
                            WHERE product like '%'+@Product+'%');

        IF @productName IS NOT NULL
            BEGIN
                DECLARE @priceDiscount float =
                    (SELECT price - @discount
                    FROM Orders  WHERE product = @productName );

                SET @result = 'THE PRODUCT: ' +
                @productName +
                ' WILL BE REDUCED TO: ' +
                CAST (@priceDiscount AS VARCHAR(50))+ ' EUROS';
            END
        ELSE
                SET @result = 'THE PRODUCT HAS NOT BEEN FOUND';
  RETURN @result
END
GO

We have declared a new variable: @productQuantity that, based on the value of @Product received as a parameter, stores the number of products found in the Orders table. If it finds more than one, it will return a message indicating to the user that they need to be more specific.

SELECT dbo.applyDiscount('P', 100)

The execution result will return the following text string: YOU SHOULD BETTER SPECIFY THE NAME OF THE PRODUCT

DROP FUNCTION

The DROP FUNCTION statement deletes the specified function from the system.

The following execution deletes the applyDiscount() function from our database.

DROP FUNCTION IF EXISTS dbo.applyDiscount;

As with other types of DROP, it is recommended to always use the IF EXISTS clause.