Page 81 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

Notes
Example: Creating and Calling T-SQL User-Defined Functions

User-Defined Functions, or UDFs, are considered as database objects that directly imitate the
semantics of functions in programming languages. Similar to a function in C#, UDFs can comprise
a variable number of input parameters and return a value of a specific type. A UDF can return
either scalar data – a string, an integer, and so forth - or tabular data.
The following UDF computes the predictable value of the inventory for a specific product. It
does so by taking in three input parameters – the UnitPrice, UnitsInStock, and Discontinued
values for a specific product – and returns a value of type money. It calculates the expected value
of the inventory by multiplying the UnitPrice by the UnitsInStock. For terminate items, this
value is halved.
CREATE FUNCTION udf_ComputeInventoryValue

(
@UnitPrice money,
@UnitsInStock smallint,

@Discontinued bit
)
RETURNS money
AS
BEGIN

DECLARE @Value decimal
SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
IF @Discontinued = 1

SET @Value = @Value * 0.5
RETURN @Value
END
Once this UDF has been added to the database, it can be located via Management Studio by
increasing the Programmability folder, then Functions, and then Scalar-value Functions. It can
be accessed in a SELECT query like so:

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
(UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC
We have added the udf_ComputeInventoryValue UDF to the Northwind database; Following
Figure displays the output of the above SELECT query when observed via Management Studio.
Make sure that the UDF is listed under the Scalar-value Functions folder in the Object Explorer.

LOVELY PROFESSIONAL UNIVERSITY 75