Stored Procedure Repeating Last Line Of Loop

Question

Stored Procedure Repeating Last Line Of Loop

skempf

SSC Enthusiast

Points: 162
More actions
July 18, 2013 at 7:29 am

#150440

I have various stored procedures that generate and email reports. They always duplicate the last line of the report. I have manually checked the query and there is no duplication of data in the results, so I assume I have a mistake in the structure of my loop.
Here is the loop from one of the stored procedures. In this example, #TempTable is filled by the query that I manually checked, so I know the data is accurate.
SELECT @ORDERCOUNT = COUNT(*) FROM #TempTable
WHILE @@ROWCOUNT <> 0
BEGIN
SET ROWCOUNT 0
SELECT @ORDERID = (SELECT TOP 1 OrderID FROM #TempTable)
SELECT @BODYLINE = '<tr><td>' + OrderID + '</td><td>' + Customer + '</td><td>' + Job + '</td><td>' + CAST(StartDate AS NVARCHAR) + '</td></tr>' FROM #TempTable WHERE OrderID = @ORDERID
SET @BODY = @BODY + @BODYLINE
DELETE #TempTable WHERE OrderID = @ORDERID
END
SET ROWCOUNT 0
DROP TABLE #TempTable
What am I doing wrong to cause the last row of data to be duplicated?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

skempf (7/18/2013)
WHILE @@ROWCOUNT <> 0 BEGIN ..... DELETE #TempTable WHERE OrderID = @ORDERID END
What am I doing wrong to cause the last row of data to be duplicated?

You have defined the loop to look at the @@Rowcount. The last DELETE statement in the WHILE is setting this value to 1. Also when the last record is being deleted. Only when the table is empty, the delete statement will set the @@Rowcount to 0.

You have to add a "SELECT count(*) FROM #TempTable" after the delete statement to get the correct @@Rowcount value.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Or even simpler would be to change the loop condition.

WHILE EXISTS(SELECT * FROM #TempTable)

You posted in the sql 7, 2000 forum. Are you actually using an old version of sql? If you are using 2005 or newer you could completely replace the loop with a different type of approach.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

skempf SSC Enthusiast Points: 162 More actions · Answer 3

Sean Lange (7/18/2013)
Or even simpler would be to change the loop condition.
WHILE EXISTS(SELECT * FROM #TempTable)
You posted in the sql 7, 2000 forum. Are you actually using an old version of sql? If you are using 2005 or newer you could completely replace the loop with a different type of approach.

This is the method that I went with and works perfectly. Thanks to both of you.

Unfortunately, I am utilizing SQL Server 2000 at our primary location. We do use 2005 and 2008 at other locations (and will hopefully eventually get our primary location upgraded), so I would be interested in hearing about the more modern solution.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

skempf (7/18/2013)
Sean Lange (7/18/2013)
Or even simpler would be to change the loop condition.
WHILE EXISTS(SELECT * FROM #TempTable)
You posted in the sql 7, 2000 forum. Are you actually using an old version of sql? If you are using 2005 or newer you could completely replace the loop with a different type of approach.
This is the method that I went with and works perfectly. Thanks to both of you.
Unfortunately, I am utilizing SQL Server 2000 at our primary location. We do use 2005 and 2008 at other locations (and will hopefully eventually get our primary location upgraded), so I would be interested in hearing about the more modern solution.

I am glad that we were able to help you figure out a solution.

I actually had cobbled together a version of this that would work in a single pass instead of looping before I realized you were posting in the 2000 forum. 🙂

The following code should do the exact same thing as your entire loop once you get to a newer version. There are obviously a few other things happening outside of the code you posted but this should be close.

select STUFF(

(

select '<tr><td>' + FName + '</td><td>'

from fakenames

where FakeNameID < 5

for xml path(''), type

).value('.', 'varchar(max)'), 1, 1, '')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/