PRINT THE RESULTS OF RECORDS DELETED

[isonlyme]

Hi,

I just want to do this:

delete from Tablename where Field in (select Newfield from SecondTable where Newfiled = 12)

(12200 records) <----this result when i want to capture it and insert in a table.

How i can achive this i tried print @@rowcount but it returns the value 1 meaning 1 row

any help appreciated!! thanks

 

[vongrunt]

@@rowcount changes after every statement. You probably have some lines of code between DELETE and SELECT @@rowcount.



------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[isonlyme]

THis is the whole Store Procedure:


delete from Tablename where Field in (select Newfield from SecondTable where Newfiled = 12)

print @@rowcount

any suggestions of a way i should do this or re-writed? I just want the result of rows affected...

thanks in advanced

 

[vongrunt]

So... there are 12200 records deleted but @@ROWCOUNT immediately after DELETE returns only 1?

Hm... any trigger behind?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[isonlyme]

no just a very straight foward delete, no triggers,

I just re-wrote it:

delete from Tablename where Field in (select Newfield from SecondTable where Newfiled = 12)

IF @@ROWCOUNT = 0
print 'Warning: No rows were deleted'
else
print @@ROWCOUNT

on the query analyzer on the message tab says this:

(1 row(s) affected)

i think is refering not of the records deleted...but just 1 line of process in the query

@@rowcount should say 1220 rows affected...?

 

[vongrunt]

> on the query analyzer on the message tab says this:

(1 row(s) affected)

Then either only one is deleted or... are you absolutely sure about triggers?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[isonlyme]

oh yeah theres no triggers, im working on the query analizer...

the way i have it is the correct way? it should work?
if not how should i re-write it?

thanks for the help!!!

 

[vongrunt]

Like I said, @@ROWCOUNT is affected by EVERY statement - in this case:

[!]if @@ROWCOUNT = 0
   print 'Warning: No rows were deleted'
else
   [!]print @@ROWCOUNT[/!]

... line in red will be affected by line with IF statement. So the safest way is to assign @@ROWCOUNT to variable immediately after tested statement:

declare @rc int

delete from Tablename where Field in (select Newfield from SecondTable where Newfiled = 12)
set @rc = @@ROWCOUNT

if @rc = 0
   print 'Warning: No rows were deleted'
else
   print @rc

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[isonlyme]

aarrrr got you, i was missing the assigment to a variable, I didnt quite get your suggestion the first time my bad...

Man,thanks a lot for the great help!!!