Union en Store procedure

Usando Union en Store Procedures SQL Server

En este artículo se muestran procedimientos almacenados en los que se han utilizado la cláusula Union del Select para mostrar un reporte que incluye totales de un campo mostrado.

Utilizando la base de datos Northwind
use Northwind
go

Ejercicio 1

Crear un procedimiento almacenado que dado un año permita generar un informe de los pedidos realizados por un empleado ese año, totalizando el monto de sus operaciones ese año.

Create or alter procedure spPedidosPorAnioEmpleado
(
@CodigoEmpleado int,
@Anio int
)
with encryption
As
Select
Str(O.OrderID) As ‘Nº Orden’,
Str(YEAR(O.OrderDate)) As ‘Año’,
Format(Sum((D.Quantity * D.UnitPrice)*(1 – D.Discount)),’###,##0.00′)
As ‘Monto Orden’
from Orders As O
join Employees As E on O.EmployeeID = E.EmployeeID
join [Order Details] As D on O.OrderID = D.OrderID
where YEAR(O.OrderDate) = @Anio and O.ShippedDate is not null
and E.EmployeeID = @CodigoEmpleado
Group by O.OrderID, YEAR(O.OrderDate)
union
select ‘Total anual’, »,
Format(Sum((D.Quantity * D.UnitPrice)*(1 – D.Discount)),’###,##0.00′) As ‘Monto Orden’
from Orders As O
join Employees As E on O.EmployeeID = E.EmployeeID
join [Order Details] As D on O.OrderID = D.OrderID
where YEAR(O.OrderDate) = @Anio and O.ShippedDate is not null
and E.EmployeeID = @CodigoEmpleado
Group by YEAR(O.OrderDate)
order by Año desc
go

Mostrar el empleado con código 1 en el año 1996

Exec spPedidosPorAnioEmpleado 1, 1996
go
La imagen muestra el resultado.

Ejercicio 2

Teniendo un empleado, crear un procedimiento donde se genere un informe de todos los pedidos realizados con el total de cada uno, totalizando el monto al final de las ventas de cada año.

Create or alter procedure spPedidosPorAnioEmpleadoTotal
(
@CodigoEmpleado int
)
with encryption
As
Select Str(O.OrderID) As ‘Nº Orden’,
YEAR(O.OrderDate) As ‘Año’,
Format(Sum((D.Quantity * D.UnitPrice)*(1 – D.Discount)),’###,##0.00′) As ‘Monto Orden’
from Orders As O
join Employees As E on O.EmployeeID = E.EmployeeID
join [Order Details] As D on O.OrderID = D.OrderID
where O.ShippedDate is not null
and E.EmployeeID = @CodigoEmpleado
Group by O.OrderID, YEAR(O.OrderDate)
union
select ‘Total anual’, year(O.OrderDate) ,
Format(Sum((D.Quantity * D.UnitPrice)*(1 – D.Discount)),’###,##0.00′) As ‘Monto Orden’
from Orders As O
join Employees As E on O.EmployeeID = E.EmployeeID
join [Order Details] As D on O.OrderID = D.OrderID
where O.ShippedDate is not null
and E.EmployeeID = @CodigoEmpleado
Group by YEAR(O.OrderDate)
order by Año
go

Mostrar los resultados para el empleado con código 2
Exec spPedidosPorAnioEmpleadoTotal 2
go

Ejercicio 3

Mostrar el detalle y total de una orden específica

Create or alter procedure spDetalleOrdenConTotal
(
@NumeroOrden int
)
with encryption
As
Select Str(O.OrderID) As ‘Nº Orden’,
STR(D.ProductID) As ‘Cód. Producto’, P.ProductName As ‘Descripción’,
STR(D.Quantity) As ‘Cantidad’, STR(D.UnitPrice) As ‘Precio’,
‘Importe’ = D.Quantity * D.UnitPrice
from Orders As O
join customers As C on O.CustomerID= C.CustomerID
join [Order Details] As D on O.OrderID = D.OrderID
join Products As P on D.ProductID = P.ProductID
where O.OrderID = @NumeroOrden
Group by O.OrderID, D.ProductID, P.ProductName, D.Quantity, D.UnitPrice
union
select ‘Total Orden’, »,»,»,»,
Format(Sum((D.Quantity * D.UnitPrice)),’###,##0.00′) As ‘Importe’
from [Order Details] As D
join Orders As O on D.OrderID = O.OrderID
join Customers As C on O.CustomerID = C.CustomerID
where O.OrderID = @NumeroOrden
Group by O.OrderID
go

Para mostrar el detalle de la orden 10248
Execute spDetalleOrdenConTotal 10248
go
La imagen muestra el resultado.