Comments by 7prince (Top 15 by date)

This error is pointing to 'AddLineItem' procedure that also EXECUTES GETDETAIL procedure.

Currently got stuck in this error:
Msg 515, Level 16, State 2, Procedure AddLineItem, Line 33
Cannot insert the value NULL into column table Salesdb.test.ORDERITEMS column does not allow nulls. INSERT fails.
The statement has been terminated.

/*
--------------------------------------------------------------------------------
The TRANSACTION, this procedure calls GetNewDetail and performs an INSERT
to the ORDERITEMS table which in turn performs an UPDATE to the INVENTORY table.
Error handling determines COMMIT/ROLLBACK.
--------------------------------------------------------------------------------
*/

IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name = 'AddLineItem')
BEGIN DROP PROCEDURE AddLineItem; END;
GO

CREATE PROCEDURE AddLineItem --with orderid, partid and qty input

@vOrderId VARCHAR(10),
@vPartId SMALLINT,
@vQty SMALLINT,
@Stockqty SMALLINT,
@vNewDetail SMALLINT OUTPUT
AS

DECLARE @vErr SMALLINT -- Error indicator
DECLARE @vErrStr VARCHAR(80) -- Hold error message

BEGIN TRANSACTION --- this is the only BEGIN TRANSACTION
SET @vErr = 0
-- Check that requested Order No exists
SELECT @vOrderid = Orderid
FROM ORDERITEMS
WHERE OrderID = @vOrderID

--Call
EXECUTE GetNewDetail @vOrderId, @vNewDetail OUTPUT;

IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
SET @vErr = 1
SET @vErrStr = 'ERROR1 in AddlineItemSP: Order Number ' + @vOrderID +
' does not exist. Unable to complete order.'
END;
ELSE
BEGIN
-- Insert order record into OrderItems table
INSERT INTO ORDERITEMS
(orderid, partid, qty)
VALUES (@vorderid,@vpartid,@vqty);
--ERROR HANDLING
IF (@@ERROR <> 0)
BEGIN
-- Error somewhere during the Insert
SET @vErr = 2
SET @vErrStr = 'ERROR2 in AddlineItemSP: Unable to Insert Order No ' + @vOrderID
END
--END IF

END;
-- END IF
-- The use of the system procedure RAISERROR allows identifying errors that occur elsewhere
-- and doing any rollback/commit here, not in triggers
IF (@vErr <> 0)
BEGIN
PRINT @vErrStr
PRINT 'Order No ' + @vOrderid +
' for ' + CONVERT(VARCHAR(4),@vQty) +
' terminated with failure.'
ROLLBACK TRANSACTION
END;
ELSE
BEGIN
PRINT @vErrStr
PRINT 'Order No ' + @vOrderid +
' for ' + CONVERT(VARCHAR(4),@vQty) +
' completed successfully'
COMMIT TRANSACTION
END;
-- END IF

-- END TRANSACTION; --leave it commented out
--END;
GO

-- No AddLineItem tests, saved for main block testing
--EXECUTE AddLineItem 1007,1001,10,4
GO