SQL Tutorial - Scalar Functions
Вставка
- Опубліковано 6 вер 2024
- 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
Would be great if you made a video on table-valued functions and their pros/cons vs CTE's
Dear B.Dev, i suggest you make font size a little bigger and close the Object Explorer so code is more visible.
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
@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?
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.
@@BeardedDevData
Ah I see. That sums it up pretty well! Thanks for the response
Hello john oliver
Hello, I don’t know who John Oliver is?
@@BeardedDevData a dude i know from somewhere and just sounds like you