CODEWITHSUNDEEP
sql-server
wergeld
wergeld

Reputation: 14442

@@ROWCOUNT Not Working Like I Think?

I am running a set of iterative SQL queries based on parameters that I determine ahead of time. Each set of queries in this .sql script are executed and insert a row into a temp table. What I am trying to do is to stop the execution of the rest of the statements once I have inserted one row into the temp table. To do this I am checking the @@ROWCOUNT value after each INSERT. If it is 0 then I continue to the next INSERT statement. If it is <> 0 then I want to stop. I am seeing some interesting effects and I am wondering if @@ROWCOUNT is altered when I check it. Here is a basic example:

create #temptable

INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=y2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=y2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=y2

What I am seeing is that when the 1st INSERT has rows it does not execute the 2nd INSERT but it does execute the 3rd. It then does not execute the 4th INSERT but it does execute the 5th, etc etc. Shouldn't it stop all further execution or is there some scope that is being changed?

Is there a way to make this SQL stop once an INSERT statement has INSERTED a row into the temp table?

Upvotes: 2

Views: 9892

Answers (4)

Danieboy
Danieboy

Reputation: 4511

I had this happen to me recently, and it was caused by "cosmetic reasons".

INSERT INTO asd
VALUES a, s, d

IF @@rowcount > 0
...

...and...

INSERT INTO asd
VALUES a, s, d
IF @@rowcount > 0
...

Are not counted as equal to each other. The extra space (looks better) between the lines resets @@rowcount for some reason.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294227

Because the @@rowcount can only be checked once, your script will skip one insert but then execute the next one. You must properly nest the IFs:

create #temptable

INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
IF @@ROWCOUNT = 0
begin
   INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
   IF @@ROWCOUNT = 0
   begin
     INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
     IF @@ROWCOUNT = 0
     begin
        INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=y2
       IF @@ROWCOUNT = 0
       begin
          INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=y2
          IF @@ROWCOUNT = 0
             INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=y2
       end
     end
   end
end

There are ways to make this more elegant (avoid the deeep nesting) by using a loop and break on the opposite condition (<>0):

create #temptable

while (1=1)
begin
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
IF @@ROWCOUNT <> 0
   break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
IF @@ROWCOUNT <> 0
   break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
IF @@ROWCOUNT <> 0
   break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=y2
IF @@ROWCOUNT <> 0
   break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=y2
IF @@ROWCOUNT <> 0
   break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=y2
break
end

Upvotes: 3

Tao
Tao

Reputation: 13996

the @@ROWCOUNT gets reset with every statement that executes, and that includes an IF statement; that's why the FIRST thing you typically do, when you care about it, is capture it into a variable so that you can do things with the value afterwards without worrying about it having changed.

UPDATE (shameless self-promotion, really): If you want to see your SQL split out into Statements automatically (so here you would see that each IF/INSERT combination constitutes one statement), you could pass it through a SQL Formatter like my PoorSQL.com site - it will put an empty line between the statements! :)

Also: nice quick overview of how to deal with this issue, which also applies to @@ERROR: http://www.novicksoftware.com/TipsAndTricks/sql-error-reset-after-every-statement.htm (although in SQL 2005 and later you should really be using try/catch rather than @@ERROR)

Upvotes: 5

Conrad Frix
Conrad Frix

Reputation: 52645

Just create a variable to hold the ROWCOUNT and then you can do what you want e.g.

DECLARE @RCount int

INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
SET @RCount = @@ROWCOUNT
IF @RCount = 0
BEGIN 
  INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
   SET @RCount = @@ROWCOUNT
END
IF @RCount = 0
BEGIN
  INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
   SET @RCount = @@ROWCOUNTE
END
....

Another option available in this case is to add a Where condition to your inserts

INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2 
                  WHERE NOT EXISTS (SELECT * FROM temptable )
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2 
                  WHERE NOT EXISTS (SELECT * FROM temptable )
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2 
                  WHERE NOT EXISTS (SELECT * FROM temptable )

Upvotes: 3

Related Questions