SQL AddlineItem procedure error for insert

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 --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

/* 

-------------------------------------------------------------------------------
/*
--------------------------------------------------------------------------------
ORDERITEMS.Detail determines new value:
You can handle NULL within the projection but it can be done in two steps
(SELECT and then test).  It is important to deal with the possibility of NULL
because the detail is part of the primary key and therefore cannot contain NULL.
--------------------------------------------------------------------------------
*/
IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name = 'GetNewDetail')
    BEGIN DROP PROCEDURE GetNewDetail; END;
GO

CREATE PROCEDURE GetNewDetail 
@vOrderid SMALLINT, --input
@vNewDetail SMALLINT OUTPUT
AS 
BEGIN 
-- Use @vOrderid (input) to get @vNewDetail (output) via a query;
  --SET @vNewDetail = ORDERITEMS.detail;
  --Get MAX detail and Add 1 to it before inserting 
   SELECT @vNewDetail =  ISNULL(MAX(ORDERITEMS.detail),1) +1
     --INTO @vNewDetail    
  FROM ORDERITEMS
  WHERE Orderid = @vOrderid;
END;
GO

-- testing block for GetNewDetail
BEGIN 
DECLARE @vOrderid CHAR(100),  -- holds value returned from procedure
        @vNewDetail CHAR(100); 
        
EXECUTE GetNewDetail 6099, @vNewDetail  OUTPUT;
--EXECUTE getnewdetail 6099, @vDetail OUTPUT;        
PRINT 'New Detail#  is for: ' + @vOrderid +  @vNewDetail ;  
--ORDER THAT HAS NO DETAIL 6007 LIKE OTHER TEST I DID
END;
GO

Tags: SQL Server 2005, SQL Server