TSQL break loop when @@ROWCOUNT = 0

Question

I have insert statements (simplified) in a SPROC like the following

SET ROWCOUNT 100

WHILE(1=1)
BEGIN

  INSERT INTO table1
  SELECT *
  FROM table2
  WHERE some_condition
  -- EDIT: Realized forgot to include this following vital line that is causing issue
  SET @var = @var + @@ROWCOUNT    

  -- @@ROWCOUNT now takes on a value of 1, which will cause the following IF check to fail even when no lines are inserted

  IF(@@ROWCOUNT = 0)
  BEGIN
    BREAK
  END

END

But the issue is, after any operation even when no more rows fit my some_condition, @@ROWCOUNT is equal to 1, not 0.

How can I break that loop when there are 0 rows returned matching my some_condition?

When you say this is "simplified", could your actual code have anything in between the INSERT and the test of @@ROWCOUNT that might have set the @@ROWCOUNT value? It might be safest to capture the @@ROWCOUNT value in an int variable immediately after the INSERT and use that variable in your test. I'm not sure about Sybase, but in SQL Server SET ROWCOUNT has been deprecated and it's recommended to use SELECT TOP (100)... — GilM, Mar 22 '13 at 22:24
The code you posted has one BEGIN and two ENDs, so the IF and the BREAK are outside of your WHILE loop. How about posting code that works? — Steve Kass, Mar 23 '13 at 02:14

score 7 · Answer 1 · answered Mar 27 '13 at 00:46

The "set" statement creates a row count of 1. What you should do is immediately save @@ROWCOUNT into a @rowCount variable and use that var later on.

declare @rowCount int

WHILE(1=1)
BEGIN

  INSERT INTO table1
  SELECT *
  FROM table2
  WHERE some_condition
  -- EDIT: Realized forgot to include this following vital line that is causing issue
  SET @rowCount = @@ROWCOUNT
  SET @var = @var + @rowCount    

  -- @@ROWCOUNT now takes on a value of 1, which will cause the following IF check to fail even when no lines are inserted

  IF(@rowCount = 0)
  BEGIN
    BREAK
  END

END

Also, you can simplify by setting @rowCount to -1 initially and changing the WHILE condition to @rowCount <> 0. The conditional BREAK will no longer be needed.

For some reason, if I store @@ROWCOUNT into another variable, then that variable will always take a value of 1. I think the `SET` statement is to blame. — czchlong, Mar 27 '13 at 20:45
in this example, if @rowCount is 1, then one record has been inserted into table1. No way around it. — Moho, Mar 27 '13 at 20:53

score 0 · Answer 2 · answered Mar 22 '13 at 21:19

0

An alternative solution. This checks each iteration to see if the ID of the last inserted record has changed or not. If it hasn't changed, it indicates that no records were added that iteration.

SET ROWCOUNT 100
declare @id int;
WHILE(1=1)

  INSERT INTO table1
  SELECT *
  FROM table2
  WHERE some_condition

  IF(@id= @@identity)
  BEGIN
    BREAK
  END
  set @id = @@identity;
END

answered Mar 22 '13 at 21:19

Brad M

7,857
1
23
40

I'm sorry but I don't understand that at all. You have `SET @ID = @@IDENTITY` but you check for `@ID = @@IDENTITY`? Could you please explain? – czchlong Mar 22 '13 at 21:23

score 0 · Answer 3 · answered Mar 25 '13 at 14:31

0

Try this solutions:

1st solution

Using @@ROWCOUNT in loop's condition.

SET ROWCOUNT 100

  INSERT INTO table1
  SELECT *
  FROM table2
  WHERE some_condition  


WHILE(@@ROWCOUNT > 0)
BEGIN

  INSERT INTO table1
  SELECT *
  FROM table2
  WHERE some_condition  

END

2nd solition

Using goto.

SET ROWCOUNT 100

WHILE(1=1)
BEGIN

  INSERT INTO table1
  SELECT *
  FROM table2
  WHERE some_condition

  IF(@@ROWCOUNT = 0)
  BEGIN
    goto label
  END

END

label1:
print 'After lopp'

answered Mar 25 '13 at 14:31

Robert

25,425
8
67
81

Hi Parado, I am currently using something similar to your second solution, but the issue is `@@ROWCOUNT` will not be `0` after the delete condition, it will instead have a value of `1`. – czchlong Mar 25 '13 at 14:39
@czchlong I don't see `delete` statement in your question. – Robert Mar 25 '13 at 14:41
Sorry, I meant the `insert` statement. – czchlong Mar 25 '13 at 14:48
You want to brake a loop after insert one row? – Robert Mar 25 '13 at 14:51

score 0 · Answer 4 · answered Mar 25 '13 at 15:12

0

I think you should use select to get the @@rowcount into a variable. try this:

declare @number_of_rows int    

SET ROWCOUNT 100

WHILE(1=1)
BEGIN

  INSERT INTO table1
  SELECT *
  FROM table2
  WHERE some_condition

  SELECT @number_of_rows=@@ROWCOUNT

  IF (@number_of_rows = 0)
  BEGIN
     BREAK
  END
END

answered Mar 25 '13 at 15:12

Ofir Winegarten

9,215
2
21
27

Hi Ofir thanks for answer. I just realized I missed a vital line after my `insert` statement that I am sure that setting `@@ROWCOUNT` to `1`. – czchlong Mar 25 '13 at 16:30

score -6 · Accepted Answer · answered Mar 28 '13 at 18:46

-6

Implemented solution similar to Moho, but used SELECT instead of SET to store @@ROWCOUNT.

answered Mar 28 '13 at 18:46

czchlong

2,434
10
51
65

1

`set @var = something` is the same as `select @var = something` – Moho Mar 29 '13 at 17:01
1

also, i'd appreciate the answer and upvote as it was my solution ;) – Moho Mar 29 '13 at 17:02
1

@Moho, Maybe it is, but I don't get the same results. – czchlong Apr 01 '13 at 20:25
2

You should have accepted the other answer, or explained exactly what the difference between them was, and what went wrong with the other one. It looks like you just didn't understand Moho's or typed it in wrong. – jwg Jun 06 '13 at 09:11

TSQL break loop when @@ROWCOUNT = 0

5 Answers5