Se debe crear una funcin
que realice el siguiente reporte,
se necesita imprimir los datos
principales de cada orden con sus
respectivas lneas de detalle,
debe utilizar cursores y manejar errores.
DROP PROCEDURE Orden_Detalle
CREATE PROCEDURE Orden_Detalle
AS
DECLARE @ProductID Integer,@UnitPrice Money,@Quantity Integer,
@orderId Integer,@CustomerId Integer,@EmployeeId Integer,@OrderDate Integer,
@RequiredDate Integer,@ShippedDate integer,@ShipVia nvarchar(25),
@ShipName nvarchar(25),@ShipAddress nvarchar(25),@ShipRegion nvarchar(25),
@ShipPostalCode integer,@ShipCountry nvarchar(25)
Begin Try
DECLARE Orden_Detalle CURSOR
FOR
SELECT Order_Details.ProductId,UnitPrice,Quantity,
Orders.OrderId,CustomerId,EmployeeId,OrderDate,
RequiredDate,ShippedDate,ShipVia,ShipName,ShipAddress,
ShipRegion,ShipPostalCode,ShipCountry
FROM Order_Details,Orders
WHERE Order_Details.OrderId = Orders.OrderID
ORDER BY Order_Details.ProductId,UnitPrice,Quantity,
Orders.OrderId,CustomerId,EmployeeId,OrderDate,
RequiredDate,ShippedDate,ShipVia,ShipName,ShipAddress,
ShipRegion,ShipPostalCode,ShipCountry
OPEN Orden_Detalle
FETCH Orden_Detalle
INTO
@ProductId,@UnitPrice,@Quantity,@OrderId,
@CustomerId,@EmployeeId,@OrderDate,
@RequiredDate,@ShippedDate,@ShipVia,
@ShipName,@ShipAddress,@ShipRegion,@ShipPostalCode,@ShipCountry
WHILE (@@FETCH_STATUS = 0 )
BEGIN
print @orderId + @CustomerId + @EmployeeId + @OrderDate +
@RequiredDate + @ShippedDate + @ShipVia +
@ShipName + @ShipAddress + @ShipRegion +
@ShipPostalCode + @ShipCountry
FETCH Orden_Detalle
INTO @orderId,@CustomerId,@EmployeeId,@OrderDate,
@RequiredDate,@ShippedDate,@ShipVia,
@ShipName,@ShipAddress,@ShipRegion,
@ShipPostalCode,@ShipCountry
END
CLOSE Orden_Detalle
DEALLOCATE Orden_Detalle
End Try
Begin Catch
Print 'Ocurrio un Problema durante la ejecucin
del Procedimiento...
Contacte con el encargado...yo no soy'
Print Error_Message()
End Catch
--EXEC Orden_Detalle
Practica de Curso