Day 83 – User-Defined Functions
These are custom functions that can be created to perform specific tasks in SQL. User-defined functions can be used to simplify complex queries or to perform calculations on data.
CREATE FUNCTION fnGetOrderTotal (@OrderID INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE @Total DECIMAL(18,2)
SELECT @Total = SUM(OrderAmount) FROM OrderDetails WHERE OrderID = @OrderID
RETURN @Total
END
SELECT OrderID, fnGetOrderTotal(OrderID) AS OrderTotal FROM Orders
In this example, a user-defined function named fnGetOrderTotal is created to retrieve the total amount of an order based on the order ID. The function is then used in a query to retrieve the total amount for each order.