Not getting require result from Store Procedure

[Ample]

I created an SP to query a temp table for a list of part numbers. The part number list would be used in a WHILE loop to return a record set of each part number row.
The part number list has 2 part numbers. The last part number is the only part number being returned.

Any help would be greatly appreciated.

CREATE PROCEDURE [dbo].[sp_Ample_TEST] AS
 
    BEGIN
    CREATE TABLE #PartNoList
        (
        partno varchar(25) NULL        
        )
 
    INSERT INTO #PartNoList (partno)
 
    SELECT DISTINCT partno
    FROM PhysicalInv
    END
 
DECLARE @partno varchar(25)
SET @partno = NULL
 
SELECT DISTINCT @partno = [partno] FROM #PartNoList ORDER BY PartNo
 
WHILE (SELECT DISTINCT @@ROWCOUNT FROM #PartNoList) > 0
    BEGIN
 
    BEGIN TRAN
 
    CREATE TABLE #ScanData
        (
        partno varchar(25) NULL,
        OHlot varchar(20) NULL,
        OHqty numeric(18,4) default 0,
        PHlot  varchar(20) NULL,
        PHonhand numeric(18,4) default 0        
        )
 
    INSERT INTO #ScanData (partno, OHlot, OHqty, PHlot, PHonhand)
 
    SELECT coalesce(dbo.InvOnHand.partno, dbo.PhysicalInv.partno) as Partno, dbo.InvOnHand.lot as OHlot, dbo.InvOnHand.onhand as OHqty, 
    dbo.physicalInv.LOT as PHlot, dbo.PhysicalInv.onhand as PHonhand
 
    FROM InvOnHand FULL OUTER JOIN  PhysicalInv ON dbo.PhysicalInv.lot = dbo.InvOnHand.lot
 
    WHERE @partno = dbo.PhysicalInv.partno OR @partno = dbo.InvOnHand.partno
 
    DELETE FROM #PartNoList WHERE partno = @partno
 
    COMMIT TRAN
 
    SET @partno = NULL
 
    SELECT DISTINCT @partno = [partno] FROM #PartNoList ORDER BY PartNo    
 
    SELECT partno, OHlot, OHqty, PHlot, PHonhand
 
    FROM #ScanData
END
GO
 
 
 

[ck9663]

This is your problem.

Even if there are two or more records on your #partnolist table, it will always return the last row and store it on your variable.

SELECT DISTINCT @partno = [partno] FROM #PartNoList ORDER BY PartNo

I did not look at your entire code. But I would assume there will be error in the way you do it. You placed the CREATE TABLE inside the WHILE LOOP. If the code inside the loop run twice, you'll have an error since the temp table is already existing. Try to reconsider another technique.

Also about the assignment of values to variable, read more about ithere.

Happy coding!

-- CK

[Ample]

Thanks CK for the tips! I moved the temp table out of the While loop and added
Top 1 to my select statement (SELECT DISTINCT Top 1 @partno = [partno] FROM #PartNoList ORDER BY PartNo). I also moved the last select statement out of the BEGIN...END block (SELECT partno, OHlot, OHqty, PHlot, PHonhand
FROM #ScanData) to achieve the desired result.

Regards