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.