SQL Tutorial - Scalar Functions скачать в хорошем качестве

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