Scalar functions in SQL
Scalar functions return a single scalar value whose data type is defined in the
Next we will see with examples , the statements to create, execute, invoke, modify and delete scalar functions.
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:
@ProductNamevariable saves the exact value of the product field of the
Orderstable filtering by the string received in the
@ProductNamehas been filled in, we use its value to save in the variable
@PriceDiscountthe result of subtraction: the database price of the product minus the value received as input parameter of
@discount. Later we set the variable
@resultwith a message indicating the product and price reduced and we return the value in the
@ProductNamehas not been filled, we return in the
@resultset with the message: NO PRODUCT FOUND
GO command written after the last
END of the function is used to encapsulate and execute independent statements from the same script. However, it is recommended that functions be declared in separate scripts.`
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:
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 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)
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
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 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 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.