Cursor result set problem

[mcshanemi]

I am attempting to update an inventory table using a cursor but my result set is only bring back 1 row instead of all rows which was what I was expecting.

Code:

CREATE PROCEDURE sp_AddComponentDetail(
@LocationID int,
@ComponentID int)

AS

DECLARE @CompID int
DECLARE @Date datetime
DECLARE @Location nvarchar(50)
DECLARE @AmtShip int
DECLARE @AmtRec int
DECLARE @Balance int
DECLARE @SortOrder int

BEGIN
DECLARE curBalance CURSOR LOCAL FAST_FORWARD
FOR SELECT dtmDate, strLocation, intAmtShip, intAmtRec, intBalance
FROM tblCurrentInventoryDetail

OPEN curBalance
FETCH NEXT FROM curBalance
INTO @Date, @Location, @AmtShip, @AmtRec, @Balance

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE tblCurrentInventoryDetail
SET intBalance = intBalance + (intAmtRec - intAmtShip)
FETCH NEXT FROM curBalance
END

CLOSE curBalance
DEALLOCATE curBalance

-- Display Results
SELECT dtmDate, strLocation, intAmtShip, intAmtRec, intBalance FROM tblCurrentInventoryDetail
RETURN
END

 

[bassguy]

hi,
First is this just psuedo code?

i do not se a reason for all of the Variables you have set in the cursor.

i do not see areason for the input params that you are using


bassguy

 

[mcshanemi]

Here is the whole stored procedure code. I am using the tblCurrentInventory to update and add component data and I am trying to get back the whole result set and not just 1 row.

CREATE    PROCEDURE sp_AddComponentDetail(
@LocationID	int,	
@ComponentID	int)

AS

DECLARE @CompID  int
DECLARE @Date   datetime
DECLARE @Location nvarchar(50)
DECLARE @AmtShip int
DECLARE @AmtRec	int
DECLARE @Balance int
DECLARE @SortOrder int

BEGIN
	DELETE FROM tblCurrentInventoryDetail

	-- Add the Shipping info to tblCurrentInventoryDetail
	INSERT INTO tblCurrentInventoryDetail ( intComponentID, dtmDate, strLocation, intAmtShip, intSortOrder ) 
        SELECT tblProducts.intComponentID, tblShipment.dtmActShip, tblLocation.strLocationDesc, tblProducts.intQtyShip, 2 
        FROM (tblShipment INNER JOIN tblLocation ON tblShipment.intDestID = tblLocation.intLocationID)
            INNER JOIN (tblComponents INNER JOIN tblProducts ON tblComponents.intComponentID = tblProducts.intComponentID)
                 ON tblShipment.intShipmentID = tblProducts.intShipmentID
        WHERE (tblShipment.intShipLocID = @LocationID) AND (tblProducts.intComponentID = @ComponentID) AND (tblShipment.ysnShipped = 1)

	-- Add the Receiving info to tblCurrentInventoryDetail
	INSERT INTO tblCurrentInventoryDetail ( intComponentID, dtmDate, strLocation, intAmtRec, intSortOrder )
        SELECT tblProducts.intComponentID, tblShipment.dtmActDelivery, tblLocation.strLocationDesc, tblProducts.intQtyRec, 3 
        FROM tblLocation INNER JOIN (tblShipment INNER JOIN (tblComponents INNER JOIN tblProducts
                ON tblComponents.intComponentID = tblProducts.intComponentID)
                ON tblShipment.intShipmentID = tblProducts.intShipmentID)
                ON tblLocation.intLocationID = tblShipment.intShipLocID
        WHERE (tblShipment.intDestID = @LocationID) AND (tblProducts.intComponentID = @ComponentID) AND (tblShipment.ysnReceived = 1)

         -- Add the Beginning Balance
         INSERT INTO tblCurrentInventoryDetail ( intComponentID, dtmDate, strLocation, intAmtRec, intSortOrder )
         SELECT tblProducts.intComponentID, tblShipment.dtmActDelivery, 'Beginning Balance' AS Location, tblProducts.intQtyRec, 1
         FROM tblShipment INNER JOIN tblProducts ON tblShipment.intShipmentID = tblProducts.intShipmentID
         WHERE (tblShipment.intRecType = 1) AND (tblShipment.intDestID = @LocationID)


	DECLARE curBalance CURSOR LOCAL FAST_FORWARD
	FOR SELECT dtmDate, strLocation, intAmtShip, intAmtRec, intBalance
	FROM tblCurrentInventoryDetail

	OPEN curBalance     
	FETCH NEXT FROM curBalance
	INTO @Date, @Location, @AmtShip, @AmtRec, @Balance

	WHILE @@FETCH_STATUS = 0
	BEGIN	
		UPDATE tblCurrentInventoryDetail
		SET intBalance = intBalance + (intAmtRec - intAmtShip)
		FETCH NEXT FROM curBalance
	END
	
	CLOSE curBalance
	DEALLOCATE curBalance

-- Display Results
SELECT dtmDate, strLocation, intAmtShip, intAmtRec, intBalance   FROM tblCurrentInventoryDetail
RETURN         
END

 

[Pattycake245]

This is because you are missing a line in your fetch next... after you update, see bold:

I am attempting to update an inventory table using a cursor but my result set is only bring back 1 row instead of all rows which was what I was expecting.

Code:

CREATE PROCEDURE sp_AddComponentDetail(
@LocationID int,
@ComponentID int)

AS

DECLARE @CompID int
DECLARE @Date datetime
DECLARE @Location nvarchar(50)
DECLARE @AmtShip int
DECLARE @AmtRec int
DECLARE @Balance int
DECLARE @SortOrder int

BEGIN
DECLARE curBalance CURSOR LOCAL FAST_FORWARD
FOR SELECT dtmDate, strLocation, intAmtShip, intAmtRec, intBalance
FROM tblCurrentInventoryDetail

OPEN curBalance
FETCH NEXT FROM curBalance
INTO @Date, @Location, @AmtShip, @AmtRec, @Balance

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE tblCurrentInventoryDetail
SET intBalance = intBalance + (intAmtRec - intAmtShip)
FETCH NEXT FROM curBalance
INTO @Date, @Location, @AmtShip, @AmtRec, @Balance
END

CLOSE curBalance
DEALLOCATE curBalance

-- Display Results
SELECT dtmDate, strLocation, intAmtShip, intAmtRec, intBalance FROM tblCurrentInventoryDetail
RETURN
END

This can probably be done as a set-based solution, rather than a cursor. If you want, show us some data from your tables and we can come up with one. As bassguy mentioned the variables being passed to the stored proc are not used, unless this is only part of the procedure.

Tim

 

[bassguy]

Hey I looked at your proc.
the update does not need a cursor

all you need is the inner update thats all

UPDATE tblCurrentInventoryDetail
        SET intBalance = intBalance + (intAmtRec - intAmtShip)

Just a tip:
I would guess you have come from a recordset or excel mindset where you think row by row. For SQL server think table (set) by table (set)


it is a bit of a switch in the thinking

HTH

Bassguy