اسنیپت‌ها، ترفندها و کدهای کاربردی

create function fn_CombineNameAndLastName(@Name nvarchar(225),@LastName nvarchar (225))     -- FUNCTION
returns nvarchar(225)
as
begin 
	return @Name+' '+@LastName
End

select dbo.fn_CombineNameAndLastName(e.FirstName,e.LastName) as FullName 
from employees e
----------------------
create function fn_GetOrderTotal(@OrderId Int)
returns float
as
begin 
	declare @Total Float -- by declare can define a value
	select @total=sum(Quantity * p.Price) 
	from order_details od 
	join products p on od.ProductID=p.ProductID
	where od.OrderID =@OrderId

	return isnull(@Total,0) -- If was null retured 0
End

select OrderID,dbo.fn_GetOrderTotal(OrderId) from orders
----------------------------
create function fn_GetCustomersByCountry(@Country varchar(225))
returns table
as
return
(
	select * from customers
	where Country=@Country
)

select * from fn_GetCustomersByCountry('Germany')
-------------------
create function fn_GetCustomerOrderSummry()
Returns @Result table -- Output
(
	CustomerId int,
	CustomerName nvarchar(225),
	TotalOrders int,
	TotalSpent float
)
as
begin
	insert into @Result
	select c.CustomerID ,c.ContactName,count(o.OrderId),sum(od.Quantity*p.Price)
	from customers c
	left join orders o on o.CustomerID= c.CustomerID
	left join order_details od on od.OrderID=o.OrderID
	left join products p on p.ProductID=od.ProductID
	group by c.CustomerID ,c.ContactName

	return;
end

select * from dbo.fn_GetCustomerOrderSummry()
-------------------------
Create function fn_GetSuppliersSalesInfo()
Returns @Info table
(
	SupplierId int,
	SupplierName varchar(225),
	TotalSalery float
)
as
begin
	declare @ProductSales Table
	(
		ProductId int,
		SupplierId int,
		TotalRevenue float
	)
	insert @ProductSales
	select p.ProductID , p.SupplierId , sum(p.price * od.Quantity) as TotalRevenue
	from products p
	left join order_details od on od.ProductID=p.ProductID
	group by p.ProductID , p.SupplierId

	insert into @Info
	select s.SupplierID , s.SupplierName , sum(ps.TotalRevenue) as TotalRevenue
	from suppliers s
	left join @ProductSales ps on ps.SupplierId=s.SupplierID
	group by s.SupplierID , s.SupplierName

	return;
end

select * from fn_GetSuppliersSalesInfo()