Existing While Loop

Question

Existing While Loop

Jason-299789

SSC-Insane

Points: 21601
More actions
March 27, 2013 at 12:07 pm

#275680

I'm looking at some code from a project that is not performing very well, and I've noticed that they have a while loop on a couple of the data gathering procedures that do a number of retries.
But theres no obvious break only a Return 0 as per below.
WHILE @Retry<@Loopcount
BEGIN
BEGIN TRY
--DO STUFF
RETURN 0
END TRY
TRY CATCH
SET @Retry=@Retry+1
END CATCH
END
Surely there needs to be a BREAK rather than a RETURN or does the RETURN act like a BREAK in this instance?
_________________________________________________________________________
SSC Guide to Posting and Best Practices

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

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

Sean Lange SSC Guru Points: 286601 More actions · Answer 1

Jason-299789 (3/27/2013)
I'm looking at some code from a project that is not performing very well, and I've noticed that they have a while loop on a couple of the data gathering procedures that do a number of retries.
But theres no obvious break only a Return 0 as per below.
WHILE @Retry<@Loopcount
BEGIN
BEGIN TRY
--DO STUFF
RETURN 0
END TRY
TRY CATCH
SET @Retry=@Retry+1
END CATCH
END
Surely there needs to be a BREAK rather than a RETURN or does the RETURN act like a BREAK in this instance?

The return will in fact return from the proc. What scares me is putting this logic in a loop. That somewhat indicates that there are far bigger issues going on. Maybe they expect deadlocks or timeouts somewhat frequently. That is only time I have seen this type of code in my experience.

This type of code always reminds me of the Einstein definition of insanity.

Insanity: doing the same thing over and over again and expecting different results.
Albert Einstein

_______________________________________________________________

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/

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

Trust me the project is insanity personified.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Sean Lange SSC Guru Points: 286601 More actions · Answer 3

Jason-299789 (3/27/2013)
Trust me the project is insanity personified.

I feel your pain.

It is painlessly easy to verify that the return will in fact exit a proc.

create proc RunOnTest

as

declare @LoopCount int = 100000000

declare @Retry int = 0

while @Retry<@Loopcount

BEGIN

BEGIN TRY

--DO STUFF

if @Retry < 10

set @Retry = 1/0 --This will obviously raise and error

select @Retry

RETURN 0

END TRY

BEGIN CATCH

print @retry

SET @Retry=@Retry+1

END CATCH

END

go

exec RunOnTest

drop proc RunOnTest

_______________________________________________________________

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/

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Thanks Sean, as for pain, I don't think you understand the meaning, the ETL I'm breaking apart runs in 7 hours, the largest dim is about 200 columns wide, and closing in on 100 million rows, and we have to find close on a 60% saving.

One of the best practices that I was taught was to always put a BREAK followed by a RETURN on the Outside of the loop, but its so long since I used a while loop in SQL I needed to double check.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Existing While Loop

Cookies on SQLServerCentral