upDiscontinuedProducts
CREATE PROC upDiscontinuedProducts
@CategoryID int,
@DiscontinuedCount int OUTPUT
AS
SELECT nc.CategoryID, nc.CategoryName,
   np.ProductName
FROM Northwind.dbo.Categories AS nc,
   Northwind.dbo.Products AS np
WHERE nc.CategoryID = np.CategoryID AND
   np.Discontinued = 1 AND
   np.CategoryID = @CategoryID
SET @DiscontinuedCount = @@ROWCOUNT
RETURN (SELECT COUNT(*)
   FROM Northwind.dbo.Products
   WHERE CategoryID = @CategoryID)