By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Not getting require result from Store Procedure

P: 6
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.

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE [dbo].[sp_Ample_TEST] AS
  2.  
  3.     BEGIN
  4.     CREATE TABLE #PartNoList
  5.         (
  6.         partno varchar(25) NULL        
  7.         )
  8.  
  9.     INSERT INTO #PartNoList (partno)
  10.  
  11.     SELECT DISTINCT partno
  12.     FROM PhysicalInv
  13.     END
  14.  
  15. DECLARE @partno varchar(25)
  16. SET @partno = NULL
  17.  
  18. SELECT DISTINCT @partno = [partno] FROM #PartNoList ORDER BY PartNo
  19.  
  20. WHILE (SELECT DISTINCT @@ROWCOUNT FROM #PartNoList) > 0
  21.     BEGIN
  22.  
  23.     BEGIN TRAN
  24.  
  25.     CREATE TABLE #ScanData
  26.         (
  27.         partno varchar(25) NULL,
  28.         OHlot varchar(20) NULL,
  29.         OHqty numeric(18,4) default 0,
  30.         PHlot  varchar(20) NULL,
  31.         PHonhand numeric(18,4) default 0        
  32.         )
  33.  
  34.     INSERT INTO #ScanData (partno, OHlot, OHqty, PHlot, PHonhand)
  35.  
  36.     SELECT coalesce(dbo.InvOnHand.partno, dbo.PhysicalInv.partno) as Partno, dbo.InvOnHand.lot as OHlot, dbo.InvOnHand.onhand as OHqty, 
  37.     dbo.physicalInv.LOT as PHlot, dbo.PhysicalInv.onhand as PHonhand
  38.  
  39.     FROM InvOnHand FULL OUTER JOIN  PhysicalInv ON dbo.PhysicalInv.lot = dbo.InvOnHand.lot
  40.  
  41.     WHERE @partno = dbo.PhysicalInv.partno OR @partno = dbo.InvOnHand.partno
  42.  
  43.     DELETE FROM #PartNoList WHERE partno = @partno
  44.  
  45.     COMMIT TRAN
  46.  
  47.     SET @partno = NULL
  48.  
  49.     SELECT DISTINCT @partno = [partno] FROM #PartNoList ORDER BY PartNo    
  50.  
  51.     SELECT partno, OHlot, OHqty, PHlot, PHonhand
  52.  
  53.     FROM #ScanData
  54. END
  55. GO
  56.  
  57.  
  58.  
Jan 16 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Jan 16 '09 #2

P: 6
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
Jan 17 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.