-- spShipOrder test harness

set nocount on;

declare @RetCode int;


execute @RetCode = spShipOrder;

print '@RetCode for no OrderID should be 1 was ' + cast(@RetCode as varchar);


execute @RetCode = spShipOrder null;

print '@RetCode for null OrderID should be 1 was ' + cast(@RetCode as varchar);


declare @invOrderID int;

select @invOrderID = max(OrderID) + 1 from Orders;

execute @RetCode = spShipOrder @invOrderID;

print '@RetCode for invalid order ' + cast(@invOrderID as varchar) + ' should be 2 was ' + cast(@RetCode as char(2));


declare @shippedOrderID int;

select top 1 @shippedOrderID = OrderID from Orders where ShippedDate is not null;

execute @RetCode = spShipOrder @shippedOrderID;

print '@RetCode for shipped order ' + cast(@shippedOrderID as varchar) + ' should be 3 was ' + cast(@RetCode as char(2));


declare @unshippedOrderID int;

select top 1 @unshippedOrderID = OrderID from Orders where ShippedDate is null;


SELECT        [Order Details].OrderID, Orders.ShippedDate, Orders.PackedBy, [Order Details].ProductID, Products.ProductName, [Order Details].Quantity, Products.UnitsInStock

FROM        [Order Details] INNER JOIN

                       Products ON [Order Details].ProductID = Products.ProductID INNER JOIN

                       Orders ON [Order Details].OrderID = Orders.OrderID

WHERE        ([Order Details].OrderID = @unshippedOrderID)


execute @RetCode = spShipOrder @unshippedOrderID;

print '@RetCode for unshipped order ' + cast(@unshippedOrderID as varchar) + ' should be 0 was ' + cast(@RetCode as char(2));


-- Show Order details

SELECT        [Order Details].OrderID, Orders.ShippedDate, Orders.PackedBy, [Order Details].ProductID, Products.ProductName, [Order Details].Quantity, Products.UnitsInStock

FROM        [Order Details] INNER JOIN

                       Products ON [Order Details].ProductID = Products.ProductID INNER JOIN

                       Orders ON [Order Details].OrderID = Orders.OrderID

WHERE        ([Order Details].OrderID = @unshippedOrderID)


-- Unship the order

update Orders

               set ShippedDate = null, PackedBy = null

               where OrderID = @unshippedOrderID;

update Products

       set Products.UnitsInStock = Products.UnitsInStock + [Order Details].Quantity

       from Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID

       where [Order Details].OrderID = @unshippedOrderID;


-- Show Order details

SELECT        [Order Details].OrderID, Orders.ShippedDate, Orders.PackedBy, [Order Details].ProductID, Products.ProductName, [Order Details].Quantity, Products.UnitsInStock

FROM        [Order Details] INNER JOIN

                       Products ON [Order Details].ProductID = Products.ProductID INNER JOIN

                       Orders ON [Order Details].OrderID = Orders.OrderID

WHERE        ([Order Details].OrderID = @unshippedOrderID)