User Defind Function| Microsoft SQL Server Online Training Session -3 | (Part-5)

Опубликовано: 04 Июнь 2024
на канале: Oracle ERP HUB - By Naz

CREATE FUNCTION TotalCalculate
(@UnitPrice decimal(10,2),@Quantity int)
RETURNS decimal(10,2)
AS
BEGIN
RETURN @UnitPrice * @Quantity
END

SELECT p.ProductName,o.UnitPrice, o.Quantity,
dbo.TotalCalculate(o.UnitPrice, o.Quantity) AS Total
FROM Product p, OrderItem o
where p.Id=o.ProductId

------2--

Alter FUNCTION applyDiscount(@product VARCHAR(55), @discount int)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @result VARCHAR(255);
DECLARE @productID int;

SET @productID = (SELECT id
FROM product
WHERE productName =@product) ;

IF @productID IS NOT NULL
BEGIN
DECLARE @dicountPrice float=
(SELECT top 1 (o.UnitPrice* o.Quantity)- @discount
FROM Orderitem o, dbo.[Order] d, OrderItem t
WHERE o.ProductId=@productID
and d.Id=o.OrderId
and t.id =o.Id
and t.OrderId=o.OrderId and t.ProductId=o.ProductId );

SET @result = 'THE PRODUCT: ' +
cast (@productID as varchar (50)) +
' IS REDUCED TO: ' +
CAST (@dicountPrice AS VARCHAR(50))+ ' Dollars';
END
ELSE
SET @result = 'THE PRODUCT HAS NOT BEEN FOUND ';
RETURN @result
END

SELECT dbo.applyDiscount('Chang', 100)

-----SQL Server inline table-valued functions

alter FUNCTION GetCustomersWithOrdersDetails ()
RETURNS @CustomersWithOrders TABLE
(CustomerID int, ContactName nvarchar(50),
Orderid int,orderdate date,city varchar(50),TotalAmount decimal(12,2))
AS
BEGIN
INSERT INTO @CustomersWithOrders
SELECT c.id, c.FirstName+' '+c.LastName,
o.id ,o.OrderDate,city,TotalAmount
FROM Customer c
JOIN dbo.[Order] o ON c.id = o.customerid

RETURN
END

SELECT * FROM [dbo].[GetCustomersWithOrdersDetails] () order by customerid

---------------------------------
We are Consultant and created channel to help the professionals those are facing critical issues. Our efforts to provide best and quick solutions to the viewer.

Subscribe this channel to get new videos alert.

На этой странице сайта вы можете посмотреть видео онлайн User Defind Function| Microsoft SQL Server Online Training Session -3 | (Part-5) длительностью часов минут секунд в хорошем качестве, которое загрузил пользователь Oracle ERP HUB - By Naz 04 Июнь 2024, поделитесь ссылкой с друзьями и знакомыми, на youtube это видео уже посмотрели 30 раз и оно понравилось 0 зрителям. Приятного просмотра!

3,094