IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'insert_orderline')
 BEGIN
  PRINT 'Dropping Procedure insert_orderline'
  DROP  PROCEDURE  insert_orderline
 END
GO

PRINT 'Creating Procedure insert_orderline'
GO

CREATE PROCEDURE insert_orderline 
 -- Add the parameters suppliedthe stored procedure here
 @OrderLineID INT OUTPUT,
 @OrderID INT, 
 @Price MONEY,
 @ProductID INT,
 @Qty FLOAT,
 @DimensionID SMALLINT
AS
BEGIN
 SET NOCOUNT ON
 
 DECLARE @ProcName SYSNAME
 SELECT @ProcName = OBJECT_NAME(@@PROCID)
 
 IF (@OrderID IS NULL)
  BEGIN
   RAISERROR(N'%s: Invalid parameters supplied, @OrderID IS NULL', 11, 1, @ProcName)
   RETURN(-100)
  END
 IF (@Price IS NULL)
  BEGIN
   RAISERROR(N'%s: Invalid parameters supplied, @Price IS NULL', 11, 1, @ProcName)
   RETURN(-100)
  END
 IF (@ProductID IS NULL)
  BEGIN
   RAISERROR(N'%s: Invalid parameters supplied, @ProductID IS NULL', 11, 1, @ProcName)
   RETURN(-100)
  END
 IF (@Qty IS NULL)
  BEGIN
   RAISERROR(N'%s: Invalid parameters supplied, @Qty IS NULL', 11, 1, @ProcName)
   RETURN(-100)
  END
 IF (@DimensionID IS NULL)
  BEGIN
   RAISERROR(N'%s: Invalid parameters supplied, @DimensionID IS NULL', 11, 1, @ProcName)
   RETURN(-100)
  END
 
 DECLARE @TranCount INT 
 DECLARE @TranName NVARCHAR(32)
 SELECT @TranName = SUBSTRING(N'TR_' + @ProcName, 1, 32)
 SELECT @TranCount = @@TRANCOUNT
 IF (@TranCount = 0)
  BEGIN TRAN @TranName
 ELSE
  SAVE TRAN @TranName 
 
 DECLARE @Error INT
 
 SELECT @OrderLineID = NULL
 
 SELECT @OrderID = [OrderID] FROM [dbo].[Order] WITH (HOLDLOCK) WHERE ([OrderID] = @OrderID)
 IF (@@ROWCOUNT = 0)
  BEGIN
   ROLLBACK TRAN @TranName
   RAISERROR(N'%s: БД не містить замовлення з ідентифіктором @OrderID = %d', 11, 1, @ProcName, @OrderID)
   RETURN(-1000)
  END
 SELECT @ProductID = [ProductID] FROM [dbo].[Product] WITH (HOLDLOCK) WHERE ([ProductID] = @ProductID)
 IF (@@ROWCOUNT = 0)
  BEGIN
   ROLLBACK TRAN @TranName
   RAISERROR(N'%s: БД не містить товару з ідентифіктором @ProductID = %d', 11, 1, @ProcName, @ProductID)
   RETURN(-2000)
  END
 SELECT @DimensionID = [DimensionID] FROM [dbo].[Dimension] WITH (HOLDLOCK) WHERE ([DimensionID] = @DimensionID)
 IF (@@ROWCOUNT = 0)
  BEGIN
   ROLLBACK TRAN @TranName
   RAISERROR(N'%s: БД не містить одиниці з ідентифіктором DimensionID = %d', 11, 1, @ProcName, @DimensionID)
   RETURN(-3000)
  END

 INSERT INTO [dbo].[OrderLine]([OrderID], [Price], [ProductID], [Qty], [DimensionID])
 VALUES(@OrderID, @Price, @ProductID, @Qty, @DimensionID)
 SELECT @Error=@@ERROR, @OrderLineID = @@IDENTITY 
 IF (@Error <> 0)
 BEGIN
  ROLLBACK TRAN @TranName
  RAISERROR(N'%s: Оператор INSERT INTO [dbo].[OrderLine] зазнав невдачі', 11, 1, @ProcName)
  RETURN(@Error)
 END
  
 IF (@TranCount = 0)
  COMMIT TRAN @TranName

 RETURN(0)
END
GO

GRANT EXEC ON insert_orderline TO PUBLIC
GO