CREATE PROCEDURE [dbo].[WF_AUTOBH]
@wfid int,
@modname varchar(20),
@modbh varchar(20) out
as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
declare
@recont int,
@modnum int,
@moddate datetime,
@moddate1 datetime,
@month_two varchar(2),
@day_two varchar(2),
@ts varchar(20)
SELECT @recont=count(*) FROM WF_BH WHERE [WFID]=@wfid and [ModName]=@modname
if (@recont<1)
BEGIN
insert WF_BH ([WFID],[ModName],[ModBH],[ModDate]) values(@wfid,@modname,0,GETDATE())
END
select @moddate1=getdate()
select @moddate=moddate,@modname=modname from WF_BH where wfid=@wfid
if (year(@moddate1)<>year(@moddate))or(month(@moddate1)<>month(@moddate))or(day(@moddate1)<>day(@moddate))
update WF_BH set moddate=getdate(),modbh=0 where modname=@modname
select @modnum=modbh from WF_BH where modname=@modname
select @modnum=1000+@modnum+1
select @ts=convert(varchar(20),@modnum)
select @ts=substring(@ts,2,3)
update WF_BH set modbh=modbh+1 where modname=@modname
select @month_two=convert(varchar(2),month(@moddate1))
if(LEN(@month_two)=1)
select @month_two='0'+@month_two
select @day_two=convert(varchar(2),day(@moddate1))
if(LEN(@day_two)=1)
select @day_two='0'+@day_two
select @modbh=@modname+substring(convert(varchar(4),year(@moddate1)),1,4)+@month_two+@day_two+@ts
COMMIT TRANSACTION
--return @modbh
--WF_BH
@wfid int,
@modname varchar(20),
@modbh varchar(20) out
as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
declare
@recont int,
@modnum int,
@moddate datetime,
@moddate1 datetime,
@month_two varchar(2),
@day_two varchar(2),
@ts varchar(20)
SELECT @recont=count(*) FROM WF_BH WHERE [WFID]=@wfid and [ModName]=@modname
if (@recont<1)
BEGIN
insert WF_BH ([WFID],[ModName],[ModBH],[ModDate]) values(@wfid,@modname,0,GETDATE())
END
select @moddate1=getdate()
select @moddate=moddate,@modname=modname from WF_BH where wfid=@wfid
if (year(@moddate1)<>year(@moddate))or(month(@moddate1)<>month(@moddate))or(day(@moddate1)<>day(@moddate))
update WF_BH set moddate=getdate(),modbh=0 where modname=@modname
select @modnum=modbh from WF_BH where modname=@modname
select @modnum=1000+@modnum+1
select @ts=convert(varchar(20),@modnum)
select @ts=substring(@ts,2,3)
update WF_BH set modbh=modbh+1 where modname=@modname
select @month_two=convert(varchar(2),month(@moddate1))
if(LEN(@month_two)=1)
select @month_two='0'+@month_two
select @day_two=convert(varchar(2),day(@moddate1))
if(LEN(@day_two)=1)
select @day_two='0'+@day_two
select @modbh=@modname+substring(convert(varchar(4),year(@moddate1)),1,4)+@month_two+@day_two+@ts
COMMIT TRANSACTION
--return @modbh
--WF_BH
GO
WF_BH 表结构WFID ModName ModBH ModDate MonthlyFlag