Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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
Posted 15-Mar-13 15:48pm
7prince409
Edited 15-Mar-13 22:45pm
gvprabu7.8K
v8
Comments
ThePhantomUpvoter at 15-Mar-13 20:57pm
   
Probably no one is going to debug your code for you, especially this much. Exactly what part of the error message is unclear to you?
7prince at 15-Mar-13 21:44pm
   
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

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 390
1 Jochen Arndt 150
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,260


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 16 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100