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