服务器端执行,主要是读取EMAIL列表,按里面的查询时间和生产厂家进行查询:
CREATE proc pr_getemail
@shpchd varchar(20)='' output
as
--向表中载入数据
--insert email_list values('石家庄以岭','abc@163.com','convert(char(8),dateadd(month,-1,getdate()),120)+''01''','convert(char(10),dateadd(day,-1,convert(char(8),getdate(),120)+''01''),120)',1,12)
--insert email_list values('云南白药','cdf@163.com','convert(char(8),dateadd(month,-1,getdate()),120)+''01''','convert(char(10),dateadd(day,-1,convert(char(8),getdate(),120)+''01''),120)',2,12)
--动态语句开始
declare @qsrq varchar(100),@jsrq varchar(200)
declare @sql varchar(8000),@sqlstring varchar(8000),@sqllast varchar(8000)
--给变量赋值
select @qsrq=qsrq,@jsrq=jsrq,@shpchd=shpchd from email_list where datepart(day,getdate())=aday and datepart(hour,getdate())=ahour
set @sql='select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw from khyy.hszy.dbo.jxdjhz a join khyy.hszy.dbo.ywmxk b on a.djbh=b.xgdjbh join khyy.hszy.dbo.spkfk c on b.spid=c.spid join khyy.hszy.dbo.mchk d on a.dwbh=d.dwbh'
set @sqlstring=' where a.rq>='+@qsrq+' and a.rq<='+@jsrq+' and c.shpchd like ''%'+@shpchd+'%'''
set @sqllast=@sql+@sqlstring
--执行并检查执行的SQL语句
exec (@sqllast)
--create table temp(rq varchar(10),danwbh varchar(11),dwmch varchar(31),spbh varchar(11),spmch varchar(31),shpgg varchar(21),shpchd varchar(21),shl int,dw varchar(4))
--insert into dbo.temp exec(@sqllast)