--测试环境构筑 set nocount on create table a ( roomid int, currentpeoplenum int ) create table b (bookinid int, roomid int, peoplenum int, checkindate varchar(10) ) insert into a select 101,1 union select 102,2 union select 103,1insert into b select 1,101,1,'2006-06-08' union select 2,102,1,'2006-06-08' union select 3,103,1,'2006-06-09'go--以下为存储过程(SP) create procedure sp_Room as update a set currentpeoplenum=currentpeoplenum+peoplenum from a inner join b on a.roomid=b.roomid and convert(datetime,checkindate)=cast(convert(varchar,getdate(),110)as datetime) go --执行前结果取出 select * from a --执行SP exec sp_room --执行后结果取出 select * from a --测试环境破弃 drop table a drop table b drop procedure sp_room goset nocount off/*测试结果 SP执行前 roomid currentpeoplenum ----------- ---------------- 101 1 102 2 103 1SP执行后(为了方便测试,我改动了部分CheckIn日期)roomid currentpeoplenum ----------- ---------------- 101 2 102 3 103 1 */--你在DTS里调用这个存储过程,然后给它设定一个每天运行的事务就可以了
set nocount on
create table a
(
roomid int,
currentpeoplenum int
)
create table b
(bookinid int,
roomid int,
peoplenum int,
checkindate varchar(10)
)
insert into a
select 101,1
union
select 102,2
union
select 103,1insert into b
select 1,101,1,'2006-06-08'
union
select 2,102,1,'2006-06-08'
union
select 3,103,1,'2006-06-09'go--以下为存储过程(SP)
create procedure sp_Room
as
update a
set currentpeoplenum=currentpeoplenum+peoplenum
from a inner join b
on a.roomid=b.roomid
and convert(datetime,checkindate)=cast(convert(varchar,getdate(),110)as datetime)
go
--执行前结果取出
select * from a
--执行SP
exec sp_room
--执行后结果取出
select * from a
--测试环境破弃
drop table a
drop table b
drop procedure sp_room
goset nocount off/*测试结果
SP执行前
roomid currentpeoplenum
----------- ----------------
101 1
102 2
103 1SP执行后(为了方便测试,我改动了部分CheckIn日期)roomid currentpeoplenum
----------- ----------------
101 2
102 3
103 1
*/--你在DTS里调用这个存储过程,然后给它设定一个每天运行的事务就可以了