Created By: Miguel López DATE: October 10, 2018 12:43 PM Tested ON: Miscrosoft SQL Server 2017
USANDO LA BASE DE DATOS NORTHWIND DESARROLLA LOS PROCEDIMIENTOS ALMACENADOS QUE REALIZEN LO SIGUIENTE.
- 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
EJECUCIÓN
DECLARE @outp VARCHAR(max) = ''
EXEC Dbo.SP_OrdenProductos 10249, @outp output
GO
SELECT @outp 'Orden10249'
MANERA ALTERNATIVA DE HACERLO SIN LA NECESIDAD DE PROCEDIMIENTOS:
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
- 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
EJECUCIÓN
SELECT CustomerID, ImporteTotal FROM Customers
GO
EXEC SP_CLIENTESIMPORTE
- 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))
EJECUCIÓN
- 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
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
- 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
EJECUCIÓN
DECLARE @ARTID INT
EXEC SP_ARTMASVENDIDOAÑO 1998, @ARTID OUTPUT
SELECT @ARTID 'ARTICULOMASVENDIDOEN1997'
- SP QUE RECIBA LA CLAVE DEL TERRITORIO Y REGRESE POR RETORNO TODOS LOS NOMBRES DE LOS EMPLEADOS QUE ATIENDEN EL TERITORIO.
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
EJECUCIÓN
SELECT TerritoryID, EmployeeID FROM EmployeeTerritories
GO
EXEC SP_TERRITORIOSEMP 02116
- 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
- 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
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
SELECT * FROM @TempTable
SELECT * FROM @TempTable ORDER BY CompanyName ASC
- 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
EJECUCIÓN
DECLARE @NPIEZAS INT, @IMPORTE NUMERIC(12,2)
EXEC SP_AÑOVENTAS 1996, @NPIEZAS OUTPUT, @IMPORTE OUTPUT
SELECT @NPIEZAS, @IMPORTE
- 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
- 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
EJECUCIÓN
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
- 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
- SP QUE RECIBA UN AÑO Y REGRESE COMO PARAMETRO DE SALIDA LA CLAVE DEL ARTICULO QUE MÁS SE VENDIO ESE AÑO Y CANTIDAD DE PIEZAS VENDIDAS DE ESE PRODUCTO EN ESE AÑO
CREATE 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
EJECUCIÓN
DECLARE @ART INT , @CAN INT
EXEC SP_ARTMASVENDIDO 1997,@ART,@CAN
SELECT @ART
SELECT @CAN
- SP que reciba la clave del empleado y retorne el total de territorio afectados
EJECUCIÓN