Help with SQL Statement - Break/Continue

Question

Help with SQL Statement - Break/Continue

SQL_Enthusiast

Hall of Fame

Points: 3805
More actions
November 4, 2010 at 3:56 pm

#226047

Essentially, I'm writing a script to create a temp table, stores data, updates the data in the temp table, and writes the new values back to the live database. Using PRINT in the current statement, here's the course of actions that takes place.
Droping Table
Table Dropped
Starting temporary table build
(27 row(s) affected)
Temporary table build complete
Updating Temporary Table with customer data
(0 row(s) affected)
Updating Temporary Table complete
Begin updating RULE (live) table with data from Temp Table
(27 row(s) affected)
Job Complete
The section that mentions (0 row(s) affected) is the part that I have concerns about. It's a real simple update statement. HOWEVER, if the results are ZERO, I need the statement to STOP or BREAK. While I have used BREAK/CONTINUE in the past, it's always around this type of statement.
DECLARE @Count int
SET @Count = 0
WHILE @Count < 100
BEGIN
PRINT 'Hello World'
SET @Count = @Count + 1
IF @Count > 10
BREAK
ELSE
CONTINUE
END
What I need is a way to take a statement like
UPDATE TempTable SET PERIOD = '1' WHERE SERVICE IN('A','B')
and if the RESUTLS are ZERO STOP. Any thoughts or am I just going to have to do it the old fashion way and run each statement one at a time and review the results?

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please tell us a few more details what you're trying to do. I'm sure (at this point) you won't need any kind of loop for the task...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Doctork11 (11/4/2010)
What I need is a way to take a statement like
UPDATE TempTable SET PERIOD = '1' WHERE SERVICE IN('A','B')
and if the RESUTLS are ZERO STOP. Any thoughts or am I just going to have to do it the old fashion way and run each statement one at a time and review the results?

Here is a clue:

declare @RowsAffected int;

UPDATE TempTable SET PERIOD = '1' WHERE SERVICE IN('A','B');

set @RowsAffected = @@ROWCOUNT;

if @RowsAffected = 0 -- do something, like stop