I have a stored procedure initiated by the user that determines records to
purge, then does a delete query.  I want the stored procedure to give some
feedback by returning the number of records that were deleted.  How do I do
that?

Using an output parameter to catch @@rowcount.

use northwind
go

create table t1 (
c1 int not null identity unique
)
go

insert into t1 default values
insert into t1 default values
insert into t1 default values
insert into t1 default values
insert into t1 default values
go

create procedure p1
@i int,
@j int,
@k int output
as
set nocount on

declare @error int

delete t1
where c1 between @i and @j

select @error = @@error, @k = @@rowcount

return @error
go

declare @k int
exec p1 2, 4, @k output

print @k
go

drop procedure p1
go

drop table t1
go


AMB


Show quoteHide quote

Use @@ROWCOUNT to return the number of rows affected by the delete. Details
on @@ROWCOUNT can be found in SQL Server Books Online.

@@rowcount contains the number of records affected by the previous insert,
update or delete.

Show quoteHide quote

View Explaination
Index Question
Warning: Null value is eliminated (causing sproc to fail)
3 Questions on db Theory/Design
restoring db without log