LAB 2

use AdventureWorksLT2012;
go

drop function if exists dbo.fnTotalVendasCliente;
go



create function dbo.fnTotalVendasCliente(@id int) returns money as
begin

return (select sum(TotalDue) from SalesLT.SalesOrderHeader where CustomerID = @id);
end
go

select dbo.fnTotalVendasCliente(29847) as "Valor total das vendas do cliente";
go


drop function if exists dbo.fnTotalVendas;
go

create function dbo.fnTotalVendas() returns money as
begin
return (select sum(TotalDue) from SalesLT.SalesOrderHeader);
end
go

select dbo.fnTotalVendas() as "Valor total de vendas";
go

drop procedure if exists spClientesCidade;
go

select CustomerID, City from SalesLT.Address inner join SalesLT.CustomerAddress on CustomerAddress.AddressID = Address.AddressID where City like 'Las Vegas';
go

drop procedure if exists spClientesCidade;
go

create procedure spClientesCidade(@cidade varchar) as
return (select CustomerID, City from SalesLT.Address inner join SalesLT.CustomerAddress on CustomerAddress.AddressID = Address.AddressID where City like @cidade);
--select * from SalesLT.SalesOrderHeader where CustomerID = 29847;