sql server programming: @@rowcount not giving correct number after if statement. -- sql server programming

@@rowcount not giving correct number after if statement.

Thomas Scheiderich
4/21/2004 11:21:16 PM

sql server programming:

I have a select statement that returns 76 rows. Following is a piece of
the procedure

***********************************************************************
exec ("select * into " + @tableName + " from openquery(north,'select *
from tblCustomers')")

if @@error <> 0
begin
print "@@error = "
print @@error
end

select @numRecordsCopied = @@rowcount
print @numRecordsCopied
***********************************************************************

As is @numRecordsCopied shows as 0.

If I take out the "if" statement down to the "end", @numRecordsCopied
correctly shows as 76.

Why does the if statement mess up the @@rowcount? I know it would be
change it if I were to do something like:

select @@error

But I am not doing that?

I did find that I could get it to work if I did the following:

***********************************************************************
exec ("select * into " + @tableName + " from openquery(north,'select *
from tblCustomers')")

select @numRecordsCopied = @@rowcount, @error = @@error

if @error <> 0
begin
print "@@error = "
print @@error
end

print @numRecordsCopied
***********************************************************************

Why is this?

Thanks,

Tom.

Re: @@rowcount not giving correct number after if statement.

Hari
4/22/2004 12:16:23 PM

Hi,

Scope of @@rowcount will be available only till the next statement after
your select statement.
Your first case you are using @@rowcount later.

You have got the right solution in the second part. SO no need to explain
more.

Thanks
Hari
MCDBA

[quoted text, click to view]