CREATE PROCEDURE dbo.MRB_24Hmail
AS
declare @mail varchar(60)
declare @mail_message varchar(500)
declare @mess nvarchar(1000)
declare @mysql varchar(1000)
declare sendmail cursor for
select E_Mail from Erpuser.MRB_EMail where 是否可用='是'
open sendmail
fetch next from sendmail into @mail
while @@fetch_status=0
begin
--print @mail
set @mysql ='select 料號, convert(char(8),投料數) as 投料數, 流程單號, 投料日期, 站別, 過帳日期, 過帳時間, 不良數, PCSPNL, 過帳人 from ufkserp.erp.erpuser.MRB_TEMP where dateadd(day,1,過帳日期)<getdate() and MRB是否接收=''是'' and 是否凍結=''否'' and 結案否=''N'' '
set @mail_message = '請將報廢物料送至MRB,謝謝!' +char(10) +char(13)+
'此信息由資訊部提供,並由系統自動發送,請勿回復 ! '
EXEC master.dbo.xp_sendmail @recipients =@mail,
@query =@mysql,-------------------選擇表
@message =@mail_message,
@copy_recipients ='B070118@uniflex-ks.com',
@subject = '請送報廢物料至MRB',
@width = 5000
fetch next from sendmail into @mail
end
close sendmail
deallocate sendmail
GO
做個范例,以後直接copy