@@rowcount not giving correct number after if statement.

[tfstom]

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.

 

[bhags]

if any assignment operation happen directly with these variables then the values get changed.

 

[tfstom]

So to get both the @@rowcount and @@error variables, you would have to do it as I have done in the second example, by doing one select statement to assign them to local variables first? Like so:

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

I assume that if I did either one of them separately, I would lose the other.

Thanks,

Tom.

 

[JayKusch]

Keep most of your code as it is in the first example. Just rework the sequqnce of events like ...

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

select @numRecordsCopied = @@rowcount
print @numRecordsCopied

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


Thanks

J. Kusch

 

[JayKusch]

or maybe this...

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

set @numRecordsCopied = @@rowcount

print CONVERT(Char,@numRecordsCopied)

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


Thanks

J. Kusch