Procedimientos.sql

USE Northwind
GO
-- PROCEDIMIENTOS ALMACENADOS
-- 1. Procedimiento que reciba la clave de la orden y regrese como parámetro de salida los nombres de los productos que se vendieron
CREATE PROC SP_OrdenProductos @NOrden INT, @NombresProd VARCHAR(max) Output
AS
BEGIN
	DECLARE @TopProd INT
	SELECT @TopProd  = MIN(ProductID) FROM [Order Details] WHERE OrderID = @NOrden
	While @TopProd IS NOT NULL
	BEGIN
		SELECT @NombresProd += P.ProductName + ', '
		FROM [Order Details] O
		INNER JOIN Products P ON P.ProductID = O.ProductID
		WHERE OrderID = @NOrden AND @TopProd = @TopProd

		SELECT @TopProd = MIN(ProductID) FROM [Order Details]
		WHERE OrderID = @NOrden AND ProductID < @TopProd
	END
	SELECT @NombresProd = SUBSTRING( @NombresProd,1, LEN(@NombresProd) - 1)
END
GO
-- EJECUCIÓN
DECLARE @outp VARCHAR(max) = ''
EXEC Dbo.SP_OrdenProductos 10249, @outp output
GO
SELECT @outp 'Orden10249'
GO
DROP PROC SP_OrdenProductos
GO
-- MANERA ALTERNATIVA DE HACERLO SIN LA NECESIDAD DE PROCEDIMIENTOS:
GO
DECLARE @nom VARCHAR(max)
SELECT @nom = ''
SELECT @nom = @nom+(case when @nom <> '' then ', ' else '' END) + P.ProductName  FROM [Order Details] O
	INNER JOIN Products P ON P.ProductID = O.ProductID
	WHERE OrderID = 10248
	ORDER BY P.ProductID ASC
SELECT @nom
GO
-- 2. AGREGAR A LA TABLA CLIENTES EL CAMPO IMPORTETOTAL, EL CUAL REPRESENTARÁ EL IMPORTE TOTAL DE VENTAS QUE HA TENIDO ESE CLIENTE. CREAR UN PROCEDIMIENTO ALMACENADO QUE LLENE DICHO CAMPO.
-- Agregar el campo ImporteTotal
ALTER TABLE Customers ADD ImporteTotal NUMERIC(12,2)
GO
-- Creación del procedimiento
CREATE PROC SP_CLIENTESIMPORTE
AS 
DECLARE @CTE VARCHAR(5), @IMPORTE NUMERIC (12,2)
SELECT @CTE = MIN(CustomerID) FROM Customers
WHILE(@CTE IS NOT NULL)
BEGIN
	SELECT @IMPORTE =  SUM(Quantity*UnitPrice) FROM Orders O
	INNER JOIN [Order Details] OD ON O.OrderID = OD.OrderID
	INNER JOIN Customers C ON O.CustomerID = C.CustomerID
	WHERE C.CustomerID = @CTE

	UPDATE Customers SET ImporteTotal = @IMPORTE WHERE CustomerID = @CTE

	SELECT @CTE = MIN(CustomerID) FROM Customers WHERE CustomerID > @CTE
END
GO
-- EJECUCIÓN
SELECT CustomerID, ImporteTotal FROM Customers
GO
EXEC SP_CLIENTESIMPORTE
GO
-- 3. SP QUE RECIBA LA CLAVE DEL EMPLEADO Y REGRESE POR RETORNO LA EDAD EXACTA DEL EMPLEADO.
CREATE PROC SP_EMPEDAD @EMP INT
AS
DECLARE @DIAS INT, @FECHAINICIO DATETIME, @CONTADOR INT = 0
SELECT @FECHAINICIO =  HireDate FROM Employees WHERE EmployeeID = @EMP
WHILE @FECHAINICIO <= GETDATE()
BEGIN 
	IF NOT(DATEPART(DW,@FECHAINICIO) IN (1,7))

GO
-- 4.-PROCEDIMIENTO ALMACENADO QUE RECIBA COMO PARAMETRO UNA FECHA Y REGRESE POR VALOR POR RETORNO EL TOTAL DE ORDENES REALIZADAS ESE DIA.
CREATE PROC SP_FECHAORDENES @AÑO INT, @MES INT, @DIA INT, @TOTALORDENES INT OUTPUT
AS
	CREATE TABLE #TABLE (ORDERID INT)
	INSERT INTO #TABLE
	SELECT OrderID 
	FROM Orders 
	WHERE YEAR(OrderDate) = @AÑO AND MONTH(OrderDate) = @MES AND DATEPART(DD,OrderDate) = @DIA

	SELECT @TOTALORDENES = ISNULL(LEN(ORDERID),0) FROM #TABLE
GO
-- EJECUCIÓN
SELECT OrderID, OrderDate FROM OrderS order by OrderDate
SELECT OrderID, OrderDate FROM Orders WHERE OrderDate = '19960801 00:00:00.000'
GO
DECLARE @NORDENES INT
EXEC SP_FECHAORDENES 1996,7,4, @NORDENES OUTPUT
SELECT @NORDENES
GO
-- 5. SP QUE RECIBA UN AÑO Y REGRESE COMO PARAMETRO DE SALIDA LA CLAVE DEL ARTICULO QUE MAS SE VENDIO ESE AÑO Y CANTIDAD DE PIEZAS VENDIDAS DE ESE PRODUCTO EN ESE AÑO.
CREATE PROC SP_ARTMASVENDIDOAÑO @FECHA INT, @CLVART INT OUTPUT
AS
	CREATE TABLE #TABLA (PRODID INT, VENTAS NUMERIC(12,2))
	INSERT INTO #TABLA
	SELECT TOP 1 P.ProductID, VENTAS = SUM(Quantity) FROM [Order Details] OD
	INNER JOIN Products P ON OD.ProductID = P.ProductID
	INNER JOIN Orders O ON OD.OrderID = O.OrderID
	WHERE YEAR(O.OrderDate) = @FECHA
	GROUP BY P.ProductID
	ORDER BY VENTAS DESC

	SELECT @CLVART = VENTAS FROM #TABLA
GO
-- EJECUCIÓN
DECLARE @ARTID INT
EXEC SP_ARTMASVENDIDOAÑO 1998, @ARTID OUTPUT
SELECT @ARTID 'ARTICULOMASVENDIDOEN1997'
GO
-- 6. SP QUE RECIBA LA CLAVE DEL TERRITORIO Y REGRESE POR RETORNO TODOS LOS NOMBRES DE LOS EMPLEADOS QUE ATIENDEN EL TERITORIO.
SELECT TerritoryID, EmployeeID FROM EmployeeTerritories
GO
CREATE PROC SP_TERRITORIOSEMP @TERRITORIO INT
AS
	SELECT FirstName FROM EmployeeTerritories ET 
	INNER JOIN Employees E ON ET.EmployeeID = E.EmployeeID
	WHERE ET.TerritoryID = @TERRITORIO
GO
-- EJECUCIÓN
SELECT TerritoryID, EmployeeID FROM EmployeeTerritories
GO
EXEC SP_TERRITORIOSEMP 02116
-- 7. SP QUE REALICE UN PROCESO DONDE REGRESE LA SIGUIENTE TABLA: NEMP | NOMBREJEFES
CREATE PROC EMPJEFES
AS
select FirstName, JefesArbol = dbo.Jefes(EmployeeID) -- FUNCIÓN QUE RETORNA LA CADENA DE JEFES EN EL ÁRBOL DE JERARQUÍA
from dbo.Employees									
GO
-- EJECUCIÓN
EXEC EMPJEFES
GO
-- 8. Procedimiento que reciba la clave del cliente e imprima cuantos productos ha vENDido en los siguientes INTervalos: 1-500, 501-1000, 1001-2000, 2001-X. Mostrar Cte, Orden e importe
DECLARE @TempTable TABLE(OrderId INT, CompanyName VARCHAR(MAX), ImporteTotal NUMERIC(12,2))
INSERT INTO @TempTable
SELECT o.OrderID,c.CustomerID, ImporteTotal = SUM(UnitPrice*Quantity*(1-Discount))
FROM Orders o --ImporteTotal = SUM(UnitPrice*Quantity*(1-Discount))
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
GROUP BY o.OrderID, c.CustomerID
GO
-- EJECUCIÓN
SELECT CompanyName,
'1-500'    = COUNT(CASE WHEN ImporteTotal BETWEEN 1 AND 500 THEN 1 END),
'501-1000' = COUNT(CASE WHEN ImporteTotal BETWEEN 501 AND 1000 THEN 1 END),
'1001-2000' = COUNT(CASE WHEN ImporteTotal BETWEEN 1001 AND 2000 THEN 1 END),
'2000+'     = COUNT(CASE WHEN ImporteTotal > 2000 THEN 1 END)
FROM @TempTable
GROUP BY CompanyName
GO
SELECT * FROM @TempTable
GO
SELECT * FROM @TempTable ORDER BY CompanyName ASC
GO
-- 9. SP que reciba un año y regrese el total de piezas vendidas y el impore total de ventas como parametro de salida
CREATE PROC SP_AÑOVENTAS @AÑO INT, @PIEZASVENDIDAS INT = 0 OUTPUT, @VENTAS NUMERIC(12,2) = 0 OUTPUT
AS
SELECT @PIEZASVENDIDAS = SUM( Quantity ), @VENTAS = SUM( Quantity*UnitPrice ) 
FROM [Order Details] OD
INNER JOIN Orders O ON OD.OrderID = O.OrderID
WHERE YEAR(O.OrderDate) = @AÑO
GO
-- EJECUCIÓN
DECLARE @NPIEZAS INT, @IMPORTE NUMERIC(12,2)
EXEC SP_AÑOVENTAS 1996, @NPIEZAS OUTPUT, @IMPORTE OUTPUT
SELECT @NPIEZAS, @IMPORTE
GO

-- 10. SP que reciba una fecha y los dias habiles, regrese como parametero de salida la fecha siguiente del dia habil.
CREATE PROC sp_DiasHabiles @fecha DATE, @dias INT
AS
DECLARE @fechaaux DATETIME, @contador INT = 0
SELECT @fechaaux=@fecha
SELECT @fechaaux=DATEADD(dd,1,@fechaaux)

WHILE @dias > =@contador
BEGIN
	IF(DATENAME(dw,@fechaaux) NOT LIKE 'Sabado' OR DATENAME(dw,@fechaaux) NOT LIKE 'Domingo')
		SELECT @contador=@contador+1
	SELECT @fechaaux=DATEADD(dd,1,@fechaaux)
END
-- EJECUCIÓN
SELECT @fechaaux
GO
EXEC sp_DiasHabiles'10-1-2015',7
GO
-- 11. SP QUE REALICE UN PROCESO DONDE REGRESE LA SIGUIENTE TABLA: 
-- | NOMEMP | NOMBREJEFES |
CREATE PROC SP_BOSSES
AS
	CREATE TABLE #TABLE (NEMP VARCHAR(MAX), NOMBRESJEFES VARCHAR(MAX))
	
	DECLARE @EmpID INT, @BossID INT, @EmpName VARCHAR(MAX), @BossName VARCHAR(MAX) = ''
	SELECT @EmpID = MIN(EmployeeID) FROM Employees
	WHILE @EmpID IS NOT NULL
	BEGIN
		SELECT @BossID = ReportsTo FROM Employees WHERE EmployeeID = @EmpID
		SELECT @EmpName  = FirstName+' '+LastName FROM Employees WHERE EmployeeID = @EmpID
		WHILE @BossID IS NOT NULL
		BEGIN
			SELECT @BossName += ', '+FirstName+' '+LastName FROM Employees WHERE EmployeeID = @BossID
			SELECT @BossID = ReportsTo FROM Employees WHERE EmployeeID = @BossID
		END
		IF LEN(@BossName) > 0
		BEGIN
			SELECT @BossName = SUBSTRING(@BossName, 3, LEN(@BossName))
		END
		INSERT INTO #TABLE VALUES (@EmpName, @BossName)
		SELECT @BossName = ''
		SELECT @EmpID = MIN(EmployeeID) FROM Employees WHERE EmployeeID > @EmpID
	END
	SELECT * FROM #TABLE
GO
SELECT E.EmployeeID, EmpName = E.FirstName +' '+E.LastName , Boss = B.EmployeeID
FROM Employees E
LEFT JOIN Employees B ON E.ReportsTo = B.EmployeeID
GO
EXEC SP_BOSSES
GO
-- 12. PROCEDIMIENTO ALMACENADO QUE LLEGUE A CREAR UN TRIGGER QUE NO PERMITA ELIMINAR REGISTROS EN TODAS LAS TABLAS
CREATE PROC SP_TRIGGER_DELETE
AS
DECLARE @MIN INT, @NOM VARCHAR(50)

SELECT @MIN = MIN(ID) FROM SYSOBJECTS
WHERE XTYPE = 'U' AND NAME NOT LIKE 'SYS%'

WHILE @MIN IN NOT NULL
BEGIN
	SELECT @NOM = NAME FROM SYSOBJECTS
	WHERE XTYPE = 'U' AND NAME NOT LIKE 'SYS%' AND id = @MIN

	PRINT 'CREATE TRIGGER TR_'+@NOM+'_DELETE'
	PRINT 'ON ['+@NOM+'] FOR DELETE AS'
	PRINT 'ROLLBACK TRANS'
	PRINT 'RAISEERROR('+CHAR(39)+') POR EL MOMENTO NO SE PUEDEN ELIMIAR REGISTROS'+CHAR(29)+',16,1)'
	
	PRINT 'GO'
	-- EXECT (@TEXTO)
	SELECT @MIN = MIN(ID) FROM SYSOBJECTS
	WHERE XTYPE = 'U' AND NAME NOT LIKE 'SYS%' AND id > @MIN
END
GO

-- 13. SP QUE RECIBA UN AÑO Y REGRESE COMO PARAMETRO DE SALIDA LA CLAVE DEL ARTICULO QUE MAS SE VENDIO ESE AÑO Y CANTIDAD DE PIEZAS VENDIDAS DE ESE PRODUCTO EN ESE AÑO.  
ALTER PROC SP_ARTMASVENDIDO @AÑO INT, @ARTID INT OUTPUT, @CANT INT OUTPUT
AS
	SELECT TOP 1 @ARTID = ISNULL(P.ProductID,0), @CANT = SUM(ISNULL(Quantity,0))
	FROM Orders O
	INNER JOIN [Order Details] OD ON O.OrderID = OD.OrderID
	INNER JOIN Products P ON OD.ProductID = P.ProductID
	WHERE YEAR(O.OrderDate) = @AÑO
	GROUP BY P.ProductID
	ORDER BY SUM(Quantity) DESC

	--SELECT @ARTID
	--SELECT @CANT
GO
DECLARE @ART INT , @CAN INT
EXEC SP_ARTMASVENDIDO 1997,@ART,@CAN
SELECT @ART
SELECT @CAN
GO
-- 14. SP que reciba la clave del empleado y retorne el total de territorio afectados