Posted by Erland Sommarskog on 11/24/05 11:53

angsql (angsql@gmail.com) writes:
> I tried this code on my test box for deleting the data, I had a very
> strange problem
>
> here is my code
>
> set nocount off
> delete from loop_projectid
>
> while 1=1
> Begin
> insert into loop_projectid
> select top 500 project_id, project_name from pds_project P
> where not exists ( select * from loop_projectid L where
> P.Project_id=L.Project_id )
> --print @@rowcount
> if @@rowcount < 500
> begin
> break
> End
> end
>
> select * from loop_projectid
> ********************************************************
>
> I get 1244 rows when i do a select, BUT if I put a print @@rowcount in
> front of IF, I get 500 rows. Could you please help me.

@@rowcount changes after each SQL statement, so if you insert a PRINT,
@@rowcount will be 0 when you come to the IF. For this reason, it is a
good habit to always save @@rowcount and @@error (which behaves the same
way) into local variables.

(By the way, rather than waiting for answers in newsgroup, this is some-
thing that can be discovered by looking up @@rowcount in Books Online.
Just a hint.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]