Stored procedure loop problem

• adamk_

  Newbie

  Points: 1

  More actions

  December 1, 2008 at 6:57 pm

  #97006

  My stored procedure seems to loop endlessly. Though I am not really seeing the problem (this is my first stored procedure in a long time). Any suggestions would be great. What I am basicaly trying to do is pick out what interests me and put it in a temp table (its not a temp atm as I want to see and use the results in another query). I am ordering by a transaction number which appears twice if the donor has given money on behalf of someone else. I want to eliminate all the rows where this is the case. If there is a easier better way to do it I will take that option too.

  ALTER PROCEDURE [dbo].[sp_PRBMoneyReturned]

  AS

  BEGIN

  --@ROWNUMCNT

  DECLARE

  @ROW1 int,

  @ROW2 int,

  @TRANNO1 int,

  @TRANNO2 int

  SELECT ROW_NUMBER() OVER (ORDER BY OriginalTransNumber) AS 'RowNumber', *

  INTO TempPRBDonation

  FROM vFRDonation

  WHERE Product = 'PRB08'

  SET @ROW1 = (SELECT TOP 1 RowNumber FROM TempPRBDonation)

  SET @ROW2 = (SELECT RowNumber FROM TempPRBDonation WHERE RowNumber = 2)

  WHILE @ROW2 <= (SELECT MAX(RowNumber) FROM TempPRBDonation)

  SET @TRANNO1 = (SELECT OriginalTransNumber FROM TempPRBDonation WHERE RowNumber = @ROW1)

  SET @TRANNO2 = (SELECT OriginalTransNumber FROM TempPRBDonation WHERE RowNumber = @ROW2)

  IF @TRANNO1 = @TRANNO2

  DELETE FROM TempPRBDonation WHERE OriginalTransNumber = @TRANNO1 AND MatchOrSoftCredit <> 'Soft Credit';

  SET @ROW1 = @ROW1 + 1

  SET @ROW2 = @ROW2 + 1

  --SELECT * FROM TempPRBDonation

  END

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  December 1, 2008 at 9:02 pm

  #906019

  This should do it:

  ALTER PROCEDURE [dbo].[sp_PRBMoneyReturned] AS

  BEGIN

  DECLARE

  @ROW1 int,

  @ROW2 int,

  @TRANNO1 int,

  @TRANNO2 int

  SELECT ROW_NUMBER() OVER (ORDER BY OriginalTransNumber) AS 'RowNumber', *

  INTO TempPRBDonation

  FROM vFRDonation

  WHERE Product = 'PRB08'

  SET @ROW1 = (SELECT TOP 1 RowNumber FROM TempPRBDonation)

  SET @ROW2 = (SELECT RowNumber FROM TempPRBDonation WHERE RowNumber = 2)

  DELETE FROM TempPRBDonation

  WHERE MatchOrSoftCredit <> 'Soft Credit'

  And RowNumber >= @ROW1

  And EXISTS( Select * From TempPRBDonation T2

  And TempPRBDonation.OriginalTransNumber = T2.OriginalTransNumber

  And TempPRBDonation.RowNumber = T2.RowNumber - (@ROW2 - @ROW1)

  )

  END

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• sgmunson

  SSC Guru

  Points: 110644

  More actions

  December 3, 2008 at 8:37 am

  #906929

  Almost, Barry... Given the methodology involved here, @ROW1 is ALWAYS going to be 1, and @ROW2 is ALWAYS going to be 2, so some of this can be greatly simplified, and I think there's an AND where there should be a WHERE:

  ALTER PROCEDURE [dbo].[sp_PRBMoneyReturned] AS

  BEGIN

  DECLARE

  @ROW1 int,

  @ROW2 int,

  @TRANNO1 int,

  @TRANNO2 int

  SELECT ROW_NUMBER() OVER (ORDER BY OriginalTransNumber) AS 'RowNumber', *

  INTO TempPRBDonation

  FROM vFRDonation

  WHERE Product = 'PRB08'

  -- SET @ROW1 = 1 -- These are unnecessary, as they're always 1 & 2

  -- SET @ROW2 = 2

  DELETE FROM TempPRBDonation

  WHERE MatchOrSoftCredit <> 'Soft Credit'

  -- And RowNumber >= @ROW1 -- This isn't needed, as @ROW1 = 1

  And EXISTS( Select * From TempPRBDonation T2

  WHERE TempPRBDonation.OriginalTransNumber = T2.OriginalTransNumber -- changed 'And' to 'WHERE' at beginning of line

  And TempPRBDonation.RowNumber = T2.RowNumber - 1 --(@ROW2 - @ROW1) --

  )

  END

  Steve

  (aka smunson)

  :):):)

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 3, 2008 at 8:56 am

  #906946

  Hi Adam

  If you run this code snippet, it will return dupe pairs. How would you identify which of each dupe pair to delete?

  SELECT *

  FROM vFRDonation a

  INNER JOIN (SELECT OriginalTransNumber

  FROM vFRDonation

  WHERE Product = 'PRB08'

  GROUP BY OriginalTransNumber

  HAVING COUNT(*) > 1

  ) b ON b.OriginalTransNumber = a.OriginalTransNumber

  Is it because the row you want to delete has MatchOrSoftCredit <> 'Soft Credit' and the row you want to retain has a different value?

  Also, is vFRDonation a table or a view?

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  December 3, 2008 at 9:25 am

  #906975

  smunson (12/3/2008)

  ---

  Almost, Barry... Given the methodology involved here, @ROW1 is ALWAYS going to be 1, and @ROW2 is ALWAYS going to be 2, so some of this can be greatly simplified, and I think there's an AND where there should be a WHERE:

  Oops, I missed that, thanks for the save.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]