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

[@darrengreenberg5522]

Would be great if you made a video on table-valued functions and their pros/cons vs CTE's

[@majoromer]

Dear B.Dev, i suggest you make font size a little bigger and close the Object Explorer so code is more visible.

[@BeardedDevData]

Will do, I’m currently looking at recording software that will let me zoom in to show the code and results even more clearly on mobile devices

[@nickt423]

@15:20 So even though it is returned in table format, the fact that it returns one value for each customer makes it a scalar function (rather than a table-valued)? Do I have that right?

[@BeardedDevData]

Almost, a table is returned because of the query not the function, if you take away the function you just have a select from a table, add the function in and you now have for each row in the table call this function and return one value.

[@nickt423]

@@BeardedDevData
Ah I see. That sums it up pretty well! Thanks for the response

[@deeedledeee]

Hello john oliver

[@BeardedDevData]

Hello, I don’t know who John Oliver is?

[@deeedledeee]

@@BeardedDevData a dude i know from somewhere and just sounds like you