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