User Defind Function| Microsoft SQL Server Online Training Session -3 | (Part-5)
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.
On this page of the site you can watch the video online User Defind Function| Microsoft SQL Server Online Training Session -3 | (Part-5) with a duration of hours minute second in good quality, which was uploaded by the user Oracle ERP HUB - By Naz 04 June 2024, share the link with friends and acquaintances, this video has already been watched 30 times on youtube and it was liked by 0 viewers. Enjoy your viewing!
