SQL Tutorial - Scalar Functions
This video will teach you how to create scalar functions in SQL Server, scalar functions only return a single value and are user-defined functions.
In this video you learn how to create scalar functions and how to use them within SQL.
CREATE FUNCTION dbo.Multiply
(
@n INT
, @m INT
)
RETURNS INT
AS
BEGIN
DECLARE @Result INT
SELECT @Result = @n * @m
RETURN @Result
END
SELECT dbo.Multiply(2, 3)
IF OBJECT_ID(N'fn_FullName', N'FN') IS NOT NULL
DROP FUNCTION fn_FullName;
GO
CREATE FUNCTION fn_FullName
(
@CustomerId INT
)
RETURNS VARCHAR(110)
AS
BEGIN
DECLARE @FullName VARCHAR(110)
SET @FullName = (SELECT REPLACE(C_First_Name + ' ' + ISNULL(C_Middle_Name, '') + ' ' + C_Last_Name, ' ', ' ') FROM dbo.Customers WHERE C_Id = @CustomerId)
RETURN @FullName
END
SELECT fn_FullName(C_Id) FROM dbo.Customers
IF OBJECT_ID(N'fn_TotalSpend', N'FN') IS NOT NULL
DROP FUNCTION fn_TotalSpend;
GO
CREATE FUNCTION dbo.fn_TotalSpend
(
@C_Id INT
)
RETURNS DECIMAL(16, 2)
AS
BEGIN
DECLARE @TotalSpend DECIMAL(16, 2)
SELECT @TotalSpend = SUM(O_Total)
FROM dbo.Customers AS A
INNER JOIN Orders AS B ON A.C_Id = B.O_C_Id
INNER JOIN Order_Details AS C ON B.O_Id = C.OD_O_Id
WHERE C_Id = @C_Id
GROUP BY C_Id
RETURN @TotalSpend
END
SELECT C_First_Name, C_Last_Name, dbo.fn_TotalSpend(C_Id) AS TotalSpend
FROM dbo.Customers
WHERE dbo.fn_TotalSpend(C_Id) IS NOT NULL