All the process runs upto ADDLINEITEM procedure and getting the error below? I am handling the NULL for the Detail column in the GETDETAIL PROCEDURE like this: SELECT @vNewDetail = ISNULL(MAX(ORDERITEMS.detail),1) +1 but still geting the same error. Not sure Why?
P.S. ADDLINEITEM procedure also EXECUTES GetNewDetail procedure below.
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
@vOrderId VARCHAR(10),
@vPartId SMALLINT,
@vQty SMALLINT,
@Stockqty SMALLINT,
@vNewDetail SMALLINT OUTPUT
AS
DECLARE @vErr SMALLINT
DECLARE @vErrStr VARCHAR(80)
BEGIN TRANSACTION
SET @vErr = 0
SELECT @vOrderid = Orderid
FROM ORDERITEMS
WHERE OrderID = @vOrderID
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 INTO ORDERITEMS
(orderid, partid, qty)
VALUES (@vorderid,@vpartid,@vqty);
IF (@@ERROR <> 0)
BEGIN
SET @vErr = 2
SET @vErrStr = 'ERROR2 in AddlineItemSP: Unable to Insert Order No ' + @vOrderID
END
END;
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;
GO
GO
IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name = 'GetNewDetail')
BEGIN DROP PROCEDURE GetNewDetail; END;
GO
CREATE PROCEDURE GetNewDetail
@vOrderid SMALLINT,
@vNewDetail SMALLINT OUTPUT
AS
BEGIN
SELECT @vNewDetail = ISNULL(MAX(ORDERITEMS.detail),1) +1
FROM ORDERITEMS
WHERE Orderid = @vOrderid;
END;
GO
BEGIN
DECLARE @vOrderid CHAR(100),
@vNewDetail CHAR(100);
EXECUTE GetNewDetail 6099, @vNewDetail OUTPUT;
PRINT 'New Detail# is for: ' + @vOrderid + @vNewDetail ;
END;
GO