Read Example - get_Order Details_1
----------------------------------------------------------------

--
-- Review  products in order
--
-------------------------------------------
-- Demo:
-- DECLARE @Status SMALLINT
-- EXEC @Status =  [get_Order Details_1] 10501
-- SELECT Status = @Status
-------------------------------------------
-- Revision History
-- Created - WFM - 01/10/2005

CREATE PROCEDURE [get_Order Details_1]
	(@OrderID_1 	[int])
AS 

-- Supress row count messages
SET NOCOUNT ON

-- Check to see if OrderID is valid
IF (SELECT Count(OrderID) from Orders WHERE OrderID = @OrderID_1) = 0
BEGIN
	PRINT 'The OrderID ' + CONVERT(varchar(30), @OrderID_1 ) + ' does not exist'
	RETURN 1
END

-- Retrieve the order details
SELECT * FROM [Order Details] WHERE  OrderID = @OrderID_1

-- Check for errors
DECLARE @Status SMALLINT
SET @Status = @@ERROR
IF @Status <> 0 
BEGIN
	-- Return error code to the calling program to indicate failure.
	PRINT 'An error occurred getting the details for the order ' + CONVERT(varchar(30), @OrderID_1) + '.'
	RETURN(@Status)
END
ELSE
BEGIN
	-- Return 0 to the calling program to indicate success.
	PRINT 'The details for the order ' + CONVERT(varchar(30), @OrderID_1) + ' were retrieved successfully.'
	RETURN(0)
END

GO


----------------------------------------------------------------
----------------------------------------------------------------


Update Example - update_Order Details_1
----------------------------------------------------------------

--
-- Adjust qnty, price, or discount for products on order
--
-- NOTE: This is script is for hints and is not a complete solution
--
-------------------------------------------
-- Demo:
-- DECLARE @Status SMALLINT
-- EXEC @Status =  [update_Order Details_1] 10501, 2
-- SELECT Status = @Status
-- 
-- SELECT * FROM [Order Details] WHERE  OrderID = 10501
-- SELECT * FROM Products WHERE ProductID = 2
-------------------------------------------
-- Revision History
-- Created - WFM - 01/10/2005

CREATE PROCEDURE [update_Order Details_1]
	(@OrderID_1 	[int],
	 @ProductID_2 	[int],
	 @NewQuantity_4  [smallint]=0,
	 @NewUnitPrice_3 [money] = 0,
	 @NewDiscount_5 [real] = 0)

AS 

-- Supress row count messages
SET NOCOUNT ON

-- Check to see if product needs to be reordered
DECLARE @UnitsInStock INT
DECLARE @ReorderLevel INT
SELECT @UnitsInStock=UnitsInStock , @ReorderLevel = ReorderLevel FROM products WHERE ProductID = @ProductID_2
IF (@UnitsInStock < @ReorderLevel)
	BEGIN
	UPDATE PRODUCTS 
	SET UnitsOnOrder = @ReorderLevel*5
	WHERE ProductID = @ProductID_2
END

-- Update Order Detail values
UPDATE [Northwind].[dbo].[Order Details] 
SET  	 [UnitPrice]	 = @NewUnitPrice_3,
	 [Quantity]	 = @NewQuantity_4,
	 [Discount]	 = @NewDiscount_5 
WHERE 
	( [OrderID]	 = @OrderID_1 AND
	 [ProductID]	 = @ProductID_2)
GO