send email with total count of record inserted and updated

[newssis]

Hi all,

i have this statement in procedure and want to do this.

if ( exists(select id from table1, table2 where table1.id = table2.id)

beging

update t1
set col = col
.
.
.
from table t1, table t2
where t2.id = t1.id

-- I want to send email total count of update

end

else

insert into table1(....)

-- send email with total count of inserted records.

end

how can i achieve this!

Thanks

 

[bborissov]

Try this:

DECLARE @TotalRows integer
DECLARE @Description varchar(200)
if exists (select id from table1, table2 where table1.id = table2.id)
   beging
       update t1
              set col = col
              ....
       from table t1, table t2
       where t2.id = t1.id
       SET @TotalRows = @@ROWCOUNT
       SET @Description = 'Updated'
   end
else
   BEGIN
       insert into table1(....)
       SET @TotalRows = @@ROWCOUNT
       SET @Description = 'Inserted'
   END

EXEC sp_send_dbmail .....
--- Check syntax in BOL for sp_send_dbmail stored procedure

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.