BI_sem14.sql

use NORTHWIND
-------------------------------
select * from Categories;
create procedure ejemplo1 @categ nvarchar(15) as
select productid, productname,
unitprice, categoryname from Products
inner join Categories on
Categories.CategoryID = .products.CategoryID
where Categoryname=@categ
----------------------------------
ejemplo1 'Condiments'
create procedure ejemplo2
as
select Categoryname as Categoría,
sum(unitprice) as precio from Products inner join
categories on Categories.CategoryID=Products.CategoryID
group by CategoryName

exec ejemplo1 'condiments'
exec ejemplo2
-----------------------------------------
create procedure ejemplo3 @categoria1 nvarchar(35),
@categoria2 nvarchar(35), @categoria3 nvarchar(35)
as
select Categoryname as Categoría,
sum(unitprice) as precio from Products inner join
categories on Categories.CategoryID=Products.CategoryID
where categoryname in(@categoria1, @categoria2, @categoria3)
group by CategoryName

exec ejemplo3 'condiments', 'confections', 'produce'
------------------------------------------------------
create procedure ejemplo4 @cateogria nvarchar(35),
@proveedor nvarchar(80) as
select categoryname as Categoria, companyname as proveedor,
sum(unitprice) as precio from Products inner join
Categories on Categories.CategoryID = Products.CategoryID
inner join Suppliers on
Suppliers.SupplierID = Products.SupplierID
where CategoryName=@cateogria and CompanyName=@proveedor
group by CategoryName,CompanyName

exec ejemplo4 'condiments', 'exotic liquids'