插入的SQL AddlineItem过程错误 [英] SQL AddlineItem procedure error for insert

问题描述

所有进程都运行到ADDLINEITEM过程并得到以下错误？我在GETDETAIL PROCEDURE中处理Detail列的NULL，如下所示：SELECT @vNewDetail = ISNULL（MAX（ORDERITEMS.detail），1）+ 1但仍然存在相同的错误。不确定为什么？



P.S. ADDLINEITEM过程还执行下面的GetNewDetail过程。



目前陷入此错误：

Msg 515，Level 16，State 2，Procedure AddLineItem，第33行

无法将值NULL插入列表Salesdb.test.ORDERITEMS'';列不允许空值。 INSERT失败。

该声明已被终止。

------------------------- -------------------------------------------------- --------------



/ *

---------- -------------------------------------------------- --------------------

TRANSACTION，此过程调用GetNewDetail并对ORDERITEMS执行INSERT

表然后对INVENTORY表执行UPDATE。

错误处理确定COMMIT / ROLLBACK。

--------------- -------------------------------------------------- ---------------

* /

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

推荐答案

尚无解决方案。我不是故意要使用这个盒子。

No solution is availabe yet. I didn''t mean to use this box.

这篇关于插入的SQL AddlineItem过程错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！