December 1, 2008 at 6:57 pm
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
December 1, 2008 at 9:02 pm
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]
December 3, 2008 at 8:37 am
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)
December 3, 2008 at 8:56 am
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
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
December 3, 2008 at 9:25 am
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]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply