sql中如何加判断,让这个存储过程只执行一次,再次点击不要重复插入数据

luckychap 2017-05-27 09:11:54
USE [CUService]
GO

/****** Object: StoredProcedure [dbo].[OA_LeaseContract_Finance_Proce_Init] Script Date: 05/27/2017 09:01:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Proc [dbo].[OA_LeaseContract_Finance_Proce_Init]
@LTID int,
@LogName varchar(30)
as

IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
DECLARE @run_flag int
SET @run_flag=0
IF EXISTS(Select 1 From OA_LeaseContract_ExMark where run_date=getdate() and run_flag=1)
SET @run_flag=1
IF @run_flag=0 --今日未执行
Begin
declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
-- ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID]
select @sysFinanceNumber=a.Sys_FinanceNumber
,@d1=a.BeginDate,@d2=a.EndDate
,@Term=a.Pay_Cyc
,@d3=a.Ahead_Date
,@PriceMonth=a.Price
from OA_LeaseContract a
where LTID=@LTID;


declare @TB table
(
[id] [int] IDENTITY(1,1) NOT NULL,
HF_Date datetime,
BeginDate datetime,
EndDate datetime,
Pay_Price numeric(18,2),
Pay_Price2 numeric(18,2),
Get_Price2 numeric(18,2),
updatee datetime
)

declare @dtmp datetime
set @dtmp=@d1
while @dtmp<=@d2
Begin
insert into @TB(BeginDate,EndDate,HF_Date,updatee)
values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE())
set @dtmp=dateadd(m,@Term,@dtmp)
End
--update @TB set updatee = CONVERT(varchar(10), GETDATE(), 23)
update @TB set Pay_Price=@PriceMonth*@Term
update @TB set EndDate=@d2 where EndDate>@d2
update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
--update @TB set HF_Date = null where id = 1
update @TB set Get_Price2 = -Pay_Price2
insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2
,CreateUser,updatetime)
select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee
from @TB a
order by a.id
Insert into OA_LeaseContract_ExMark values (1,GETDATE())
update OA_LeaseContract_ExMark set run_date=CONVERT(varchar(10), GETDATE(), 23)
End


-- End
--select top 1 * from @TB order by BeginDate desc

GO

...全文
739 27 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
程序猿老曾 2017-05-31
  • 打赏
  • 举报
回复
Select 1 From OA_LeaseContract_ExMark where run_date=getdate() and run_flag=1 不知道你这个run_date是不是datetime类型~~~ 你可以这么写 Select 1 From OA_LeaseContract_ExMark where convert(nvarchar(10),run_date,120)=convert(nvarchar(10),getdate(),120) and run_flag=1 这样就能去到当前日期的记录了
luckychap 2017-05-27
  • 打赏
  • 举报
回复
引用 8 楼 appetizing_fish1 的回复:
[quote=引用 7 楼 appetizing_fish1 的回复:]
OA_LeaseContract_ExMarked 表中有没有记录到数据 ?


这条语句有没有插入成功 ?

Insert into OA_LeaseContract_ExMarked values (1,GETDATE()) [/quote]

有记录到,时间还是带秒的,如上图
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
引用 7 楼 appetizing_fish1 的回复:
OA_LeaseContract_ExMarked 表中有没有记录到数据 ?
这条语句有没有插入成功 ? Insert into OA_LeaseContract_ExMarked values (1,GETDATE())
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
OA_LeaseContract_ExMarked 表中有没有记录到数据 ?
luckychap 2017-05-27
  • 打赏
  • 举报
回复
引用 5 楼 appetizing_fish1 的回复:
[quote=引用 4 楼 appetizing_fish1 的回复:] [quote=引用 3 楼 qq_38287011 的回复:] [quote=引用 2 楼 appetizing_fish1 的回复:] 楼主,我在你上个贴中说了要转化getdate()成标准日期的, 还提供了转化函数 dbo.FormatDate(getdate()) 引用 4 楼 appetizing_fish1 的回复: getdate()需要转化成标准日期 , 比如 2017-05-25 15:14:21.167 要转成 2017-05-25 , 要不然取不到值 ALTER FUNCTION [dbo].[FormatDate] (@CurDate datetime) RETURNS datetime AS BEGIN return cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2)) END
嗯。。关键是出错啊。。没有Formatdate这个表。。[/quote] 因为你没有建立这个函数 将ALTER 改成 CREATE , 然后在对应的数据库上Run一下.



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDate]

GO

CREATE FUNCTION [dbo].[FormatDate](@CurDate datetime)
            RETURNS datetime
AS

BEGIN
        return  cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2))
END

GO

[/quote] 判断是否运行的语句日期加上函数 FormatDate 如下 IF EXISTS(Select 1 From cRunFlagTbl where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1) SET @run_flag=1 [/quote] USE [CUService] GO /****** Object: StoredProcedure [dbo].[OA_LeaseContract_Finance_Proce_Init] Script Date: 05/25/2017 15:35:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Alter Proc [dbo].[OA_LeaseContract_Finance_Proce_Init] @LTID int, @LogName varchar(30) -- @run_flag int as Begin -- IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID ) DECLARE @run_flag int --set @run_flag=0 IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1) SET @run_flag=1 IF @run_flag=0 --今日未执行 -- Begin declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint -- ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID] select @sysFinanceNumber=a.Sys_FinanceNumber ,@d1=a.BeginDate,@d2=a.EndDate ,@Term=a.Pay_Cyc ,@d3=a.Ahead_Date ,@PriceMonth=a.Price from OA_LeaseContract a where LTID=@LTID; declare @TB table ( [id] [int] IDENTITY(1,1) NOT NULL, HF_Date datetime, BeginDate datetime, EndDate datetime, Pay_Price numeric(18,2), Pay_Price2 numeric(18,2), Get_Price2 numeric(18,2), updatee datetime ) declare @dtmp datetime set @dtmp=@d1 while @dtmp<=@d2 Begin insert into @TB(BeginDate,EndDate,HF_Date,updatee) values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE()) set @dtmp=dateadd(m,@Term,@dtmp) End --update @TB set updatee = CONVERT(varchar(10), GETDATE(), 23) update @TB set Pay_Price=@PriceMonth*@Term update @TB set EndDate=@d2 where EndDate>@d2 update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2 update @TB set Pay_Price2 = @PriceMonth*2 where id = 1 update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2 --update @TB set HF_Date = null where id = 1 update @TB set Get_Price2 = -Pay_Price2 insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate ,Pay_Price,Pay_Price2,Get_Price2 ,CreateUser,updatetime) select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate ,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee from @TB a order by a.id Insert into OA_LeaseContract_ExMarked values (1,GETDATE()) update OA_LeaseContract_Finance set Datelines = GETDATE() update OA_LeaseContract_ExMarked set run_date=CONVERT(varchar(10), GETDATE(), 23) End -- End --select top 1 * from @TB order by BeginDate desc GO exec OA_LeaseContract_Finance_Proce_Init 100001,'asd' select * from OA_LeaseContract_Finance 还是不对啊,还是重复执行,那个表我也创建了
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
引用 4 楼 appetizing_fish1 的回复:
[quote=引用 3 楼 qq_38287011 的回复:] [quote=引用 2 楼 appetizing_fish1 的回复:] 楼主,我在你上个贴中说了要转化getdate()成标准日期的, 还提供了转化函数 dbo.FormatDate(getdate()) 引用 4 楼 appetizing_fish1 的回复: getdate()需要转化成标准日期 , 比如 2017-05-25 15:14:21.167 要转成 2017-05-25 , 要不然取不到值 ALTER FUNCTION [dbo].[FormatDate] (@CurDate datetime) RETURNS datetime AS BEGIN return cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2)) END
嗯。。关键是出错啊。。没有Formatdate这个表。。[/quote] 因为你没有建立这个函数 将ALTER 改成 CREATE , 然后在对应的数据库上Run一下.



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDate]

GO

CREATE FUNCTION [dbo].[FormatDate](@CurDate datetime)
            RETURNS datetime
AS

BEGIN
        return  cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2))
END

GO

[/quote] 判断是否运行的语句日期加上函数 FormatDate 如下 IF EXISTS(Select 1 From cRunFlagTbl where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1) SET @run_flag=1
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
引用 3 楼 qq_38287011 的回复:
[quote=引用 2 楼 appetizing_fish1 的回复:] 楼主,我在你上个贴中说了要转化getdate()成标准日期的, 还提供了转化函数 dbo.FormatDate(getdate()) 引用 4 楼 appetizing_fish1 的回复: getdate()需要转化成标准日期 , 比如 2017-05-25 15:14:21.167 要转成 2017-05-25 , 要不然取不到值 ALTER FUNCTION [dbo].[FormatDate] (@CurDate datetime) RETURNS datetime AS BEGIN return cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2)) END
嗯。。关键是出错啊。。没有Formatdate这个表。。[/quote] 因为你没有建立这个函数 将ALTER 改成 CREATE , 然后在对应的数据库上Run一下.



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDate]

GO

CREATE FUNCTION [dbo].[FormatDate](@CurDate datetime)
            RETURNS datetime
AS

BEGIN
        return  cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2))
END

GO

luckychap 2017-05-27
  • 打赏
  • 举报
回复
引用 2 楼 appetizing_fish1 的回复:
楼主,我在你上个贴中说了要转化getdate()成标准日期的, 还提供了转化函数 dbo.FormatDate(getdate()) 引用 4 楼 appetizing_fish1 的回复: getdate()需要转化成标准日期 , 比如 2017-05-25 15:14:21.167 要转成 2017-05-25 , 要不然取不到值 ALTER FUNCTION [dbo].[FormatDate] (@CurDate datetime) RETURNS datetime AS BEGIN return cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2)) END
嗯。。关键是出错啊。。没有Formatdate这个表。。
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
楼主,我在你上个贴中说了要转化getdate()成标准日期的, 还提供了转化函数 dbo.FormatDate(getdate()) 引用 4 楼 appetizing_fish1 的回复: getdate()需要转化成标准日期 , 比如 2017-05-25 15:14:21.167 要转成 2017-05-25 , 要不然取不到值 ALTER FUNCTION [dbo].[FormatDate] (@CurDate datetime) RETURNS datetime AS BEGIN return cast(year(@CurDate) as varchar(4))+'-'+cast(month(@CurDate) as varchar(2))+'-'+cast(day(@CurDate) as varchar(2)) END
leo_lesley 2017-05-27
  • 打赏
  • 举报
回复

  IF EXISTS(Select 1 From OA_LeaseContract_ExMark where run_date=getdate() and run_flag=1)

-- 这句写得有问题,  getdate 函数取出来的值是带时间的,你要想比对当天的数据要把后面的时间去掉,可以试试下面的语句

 IF EXISTS(Select 1 From OA_LeaseContract_ExMark where datediff(day,run_date,getdate()) = 0 and run_flag=1)
luckychap 2017-05-27
  • 打赏
  • 举报
回复
引用 25 楼 appetizing_fish1 的回复:
[quote=引用 24 楼 appetizing_fish1 的回复:] [quote=引用 23 楼 qq_38287011 的回复:] [quote=引用 22 楼 appetizing_fish1 的回复:] 1. 那没道理啊, 怎么看你的贴图, 少了Begin ? , 或者在 Begin 下面再测一下@run_flag的值 IF @run_flag=0 --今日未执行 Begin Select @run_flag
执行不出来了。。[/quote] 那这就说明没有执行下面这些代码, 程序没问题啊. [/quote] 只是不知你这显示的空结果集是怎么来的 ?[/quote] 这个空结果, exec OA_LeaseContract_Finance_Proce_Init 100011,'asd' select * from OA_LeaseContract_Finance 是最底下这句,我没截到图,您可能没看到,最底下有这句
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
引用 24 楼 appetizing_fish1 的回复:
[quote=引用 23 楼 qq_38287011 的回复:] [quote=引用 22 楼 appetizing_fish1 的回复:] 1. 那没道理啊, 怎么看你的贴图, 少了Begin ? , 或者在 Begin 下面再测一下@run_flag的值 IF @run_flag=0 --今日未执行 Begin Select @run_flag
执行不出来了。。[/quote] 那这就说明没有执行下面这些代码, 程序没问题啊. [/quote] 只是不知你这显示的空结果集是怎么来的 ?
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
引用 23 楼 qq_38287011 的回复:
[quote=引用 22 楼 appetizing_fish1 的回复:] 1. 那没道理啊, 怎么看你的贴图, 少了Begin ? , 或者在 Begin 下面再测一下@run_flag的值 IF @run_flag=0 --今日未执行 Begin Select @run_flag
执行不出来了。。[/quote] 那这就说明没有执行下面这些代码, 程序没问题啊.
luckychap 2017-05-27
  • 打赏
  • 举报
回复
引用 22 楼 appetizing_fish1 的回复:
1. 那没道理啊, 怎么看你的贴图, 少了Begin ? , 或者在 Begin 下面再测一下@run_flag的值
IF @run_flag=0 --今日未执行
Begin
Select @run_flag


执行不出来了。。
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
1. 那没道理啊, 怎么看你的贴图, 少了Begin ? , 或者在 Begin 下面再测一下@run_flag的值 IF @run_flag=0 --今日未执行 Begin Select @run_flag
luckychap 2017-05-27
  • 打赏
  • 举报
回复
引用 19 楼 appetizing_fish1 的回复:
[quote=引用 18 楼 qq_38287011 的回复:]
[quote=引用 17 楼 appetizing_fish1 的回复:]
试试这个



USE [CUService]
GO


Alter Proc [dbo].[OA_LeaseContract_Finance_Proce_Init]
@LTID int,
@LogName varchar(30)
-- @run_flag int
as
Begin
-- IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
DECLARE @run_flag int
set @run_flag=0
IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
SET @run_flag=1


IF @run_flag=0 --今日未执行
Begin
declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
-- ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID]
select @sysFinanceNumber=a.Sys_FinanceNumber
,@d1=a.BeginDate,@d2=a.EndDate
,@Term=a.Pay_Cyc
,@d3=a.Ahead_Date
,@PriceMonth=a.Price
from OA_LeaseContract a
where LTID=@LTID;


declare @TB table
(
[id] [int] IDENTITY(1,1) NOT NULL,
HF_Date datetime,
BeginDate datetime,
EndDate datetime,
Pay_Price numeric(18,2),
Pay_Price2 numeric(18,2),
Get_Price2 numeric(18,2),
updatee datetime
)

declare @dtmp datetime
set @dtmp=@d1
while @dtmp<=@d2
Begin
insert into @TB(BeginDate,EndDate,HF_Date,updatee)
values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE())
set @dtmp=dateadd(m,@Term,@dtmp)
End


--update @TB set updatee = CONVERT(varchar(10), GETDATE(), 23)
update @TB set Pay_Price=@PriceMonth*@Term
update @TB set EndDate=@d2 where EndDate>@d2
update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
--update @TB set HF_Date = null where id = 1
update @TB set Get_Price2 = -Pay_Price2
insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2
,CreateUser,updatetime)
select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee
from @TB a
order by a.id

Insert into OA_LeaseContract_ExMarked values (1,GETDATE())
update OA_LeaseContract_Finance set Datelines = GETDATE()
update OA_LeaseContract_ExMarked set run_date=CONVERT(varchar(10), GETDATE(), 23)
End
End

GO



还是可以重复执行。。好郁闷[/quote]


DECLARE @run_flag int
set @run_flag=0
IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
SET @run_flag=1

这里测试一下,看看@run_flag的值是多少 ?
Select @run_flag



[/quote]

这是是null
luckychap 2017-05-27
  • 打赏
  • 举报
回复
引用 19 楼 appetizing_fish1 的回复:
[quote=引用 18 楼 qq_38287011 的回复:]
[quote=引用 17 楼 appetizing_fish1 的回复:]
试试这个



USE [CUService]
GO


Alter Proc [dbo].[OA_LeaseContract_Finance_Proce_Init]
@LTID int,
@LogName varchar(30)
-- @run_flag int
as
Begin
-- IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
DECLARE @run_flag int
set @run_flag=0
IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
SET @run_flag=1


IF @run_flag=0 --今日未执行
Begin
declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
-- ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID]
select @sysFinanceNumber=a.Sys_FinanceNumber
,@d1=a.BeginDate,@d2=a.EndDate
,@Term=a.Pay_Cyc
,@d3=a.Ahead_Date
,@PriceMonth=a.Price
from OA_LeaseContract a
where LTID=@LTID;


declare @TB table
(
[id] [int] IDENTITY(1,1) NOT NULL,
HF_Date datetime,
BeginDate datetime,
EndDate datetime,
Pay_Price numeric(18,2),
Pay_Price2 numeric(18,2),
Get_Price2 numeric(18,2),
updatee datetime
)

declare @dtmp datetime
set @dtmp=@d1
while @dtmp<=@d2
Begin
insert into @TB(BeginDate,EndDate,HF_Date,updatee)
values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE())
set @dtmp=dateadd(m,@Term,@dtmp)
End


--update @TB set updatee = CONVERT(varchar(10), GETDATE(), 23)
update @TB set Pay_Price=@PriceMonth*@Term
update @TB set EndDate=@d2 where EndDate>@d2
update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
--update @TB set HF_Date = null where id = 1
update @TB set Get_Price2 = -Pay_Price2
insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2
,CreateUser,updatetime)
select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee
from @TB a
order by a.id

Insert into OA_LeaseContract_ExMarked values (1,GETDATE())
update OA_LeaseContract_Finance set Datelines = GETDATE()
update OA_LeaseContract_ExMarked set run_date=CONVERT(varchar(10), GETDATE(), 23)
End
End

GO



还是可以重复执行。。好郁闷[/quote]


DECLARE @run_flag int
set @run_flag=0
IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
SET @run_flag=1

这里测试一下,看看@run_flag的值是多少 ?
Select @run_flag



[/quote]

结果是1
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
引用 18 楼 qq_38287011 的回复:
[quote=引用 17 楼 appetizing_fish1 的回复:] 试试这个


USE [CUService]
GO


Alter Proc [dbo].[OA_LeaseContract_Finance_Proce_Init]
@LTID int,
@LogName varchar(30)
   -- @run_flag int	
as
Begin
			  -- IF  EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
			   DECLARE @run_flag int 
			   set @run_flag=0
			   IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
			                                  SET @run_flag=1

			                                  
			   IF @run_flag=0   --今日未执行
				   Begin
							declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
							  --  ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID]
							select @sysFinanceNumber=a.Sys_FinanceNumber
							,@d1=a.BeginDate,@d2=a.EndDate
							,@Term=a.Pay_Cyc
							,@d3=a.Ahead_Date
							,@PriceMonth=a.Price
							from OA_LeaseContract a 
							where LTID=@LTID;


							declare @TB table
							(
									[id] [int] IDENTITY(1,1) NOT NULL,
									HF_Date datetime,  
									BeginDate datetime,
									EndDate datetime,
									Pay_Price numeric(18,2),
									Pay_Price2 numeric(18,2),
									Get_Price2 numeric(18,2),
									updatee datetime
							)

							declare @dtmp datetime
							set @dtmp=@d1
							while @dtmp<=@d2
								Begin   
									insert into @TB(BeginDate,EndDate,HF_Date,updatee)
									values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE())
									set @dtmp=dateadd(m,@Term,@dtmp)
								End
				  
				  
							--update @TB set updatee =  CONVERT(varchar(10), GETDATE(), 23)
							update @TB set Pay_Price=@PriceMonth*@Term
							update @TB set EndDate=@d2 where EndDate>@d2
							update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
							update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
							update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
							--update @TB set HF_Date = null where id = 1
							update @TB set Get_Price2 = -Pay_Price2 
							insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate
							,Pay_Price,Pay_Price2,Get_Price2
							,CreateUser,updatetime)
							select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
							,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee
							from @TB a
							order by a.id
							
							Insert into OA_LeaseContract_ExMarked values (1,GETDATE()) 
							update OA_LeaseContract_Finance set Datelines = GETDATE()
							update OA_LeaseContract_ExMarked set run_date=CONVERT(varchar(10), GETDATE(), 23)
			        End
   End
   
GO


还是可以重复执行。。好郁闷[/quote] DECLARE @run_flag int set @run_flag=0 IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1) SET @run_flag=1 这里测试一下,看看@run_flag的值是多少 ? Select @run_flag
luckychap 2017-05-27
  • 打赏
  • 举报
回复
引用 17 楼 appetizing_fish1 的回复:
试试这个


USE [CUService]
GO


Alter Proc [dbo].[OA_LeaseContract_Finance_Proce_Init]
@LTID int,
@LogName varchar(30)
   -- @run_flag int	
as
Begin
			  -- IF  EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
			   DECLARE @run_flag int 
			   set @run_flag=0
			   IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
			                                  SET @run_flag=1

			                                  
			   IF @run_flag=0   --今日未执行
				   Begin
							declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
							  --  ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID]
							select @sysFinanceNumber=a.Sys_FinanceNumber
							,@d1=a.BeginDate,@d2=a.EndDate
							,@Term=a.Pay_Cyc
							,@d3=a.Ahead_Date
							,@PriceMonth=a.Price
							from OA_LeaseContract a 
							where LTID=@LTID;


							declare @TB table
							(
									[id] [int] IDENTITY(1,1) NOT NULL,
									HF_Date datetime,  
									BeginDate datetime,
									EndDate datetime,
									Pay_Price numeric(18,2),
									Pay_Price2 numeric(18,2),
									Get_Price2 numeric(18,2),
									updatee datetime
							)

							declare @dtmp datetime
							set @dtmp=@d1
							while @dtmp<=@d2
								Begin   
									insert into @TB(BeginDate,EndDate,HF_Date,updatee)
									values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE())
									set @dtmp=dateadd(m,@Term,@dtmp)
								End
				  
				  
							--update @TB set updatee =  CONVERT(varchar(10), GETDATE(), 23)
							update @TB set Pay_Price=@PriceMonth*@Term
							update @TB set EndDate=@d2 where EndDate>@d2
							update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
							update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
							update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
							--update @TB set HF_Date = null where id = 1
							update @TB set Get_Price2 = -Pay_Price2 
							insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate
							,Pay_Price,Pay_Price2,Get_Price2
							,CreateUser,updatetime)
							select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
							,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee
							from @TB a
							order by a.id
							
							Insert into OA_LeaseContract_ExMarked values (1,GETDATE()) 
							update OA_LeaseContract_Finance set Datelines = GETDATE()
							update OA_LeaseContract_ExMarked set run_date=CONVERT(varchar(10), GETDATE(), 23)
			        End
   End
   
GO


还是可以重复执行。。好郁闷
顺势而为1 2017-05-27
  • 打赏
  • 举报
回复
试试这个


USE [CUService]
GO


Alter Proc [dbo].[OA_LeaseContract_Finance_Proce_Init]
@LTID int,
@LogName varchar(30)
   -- @run_flag int	
as
Begin
			  -- IF  EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
			   DECLARE @run_flag int 
			   set @run_flag=0
			   IF EXISTS(Select 1 From OA_LeaseContract_ExMarked where dbo.FormatDate(run_date)=dbo.FormatDate(getdate()) and run_flag=1)
			                                  SET @run_flag=1

			                                  
			   IF @run_flag=0   --今日未执行
				   Begin
							declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
							  --  ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF_OA_LeaseContract_Finance_LTID]
							select @sysFinanceNumber=a.Sys_FinanceNumber
							,@d1=a.BeginDate,@d2=a.EndDate
							,@Term=a.Pay_Cyc
							,@d3=a.Ahead_Date
							,@PriceMonth=a.Price
							from OA_LeaseContract a 
							where LTID=@LTID;


							declare @TB table
							(
									[id] [int] IDENTITY(1,1) NOT NULL,
									HF_Date datetime,  
									BeginDate datetime,
									EndDate datetime,
									Pay_Price numeric(18,2),
									Pay_Price2 numeric(18,2),
									Get_Price2 numeric(18,2),
									updatee datetime
							)

							declare @dtmp datetime
							set @dtmp=@d1
							while @dtmp<=@d2
								Begin   
									insert into @TB(BeginDate,EndDate,HF_Date,updatee)
									values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3,GETDATE())
									set @dtmp=dateadd(m,@Term,@dtmp)
								End
				  
				  
							--update @TB set updatee =  CONVERT(varchar(10), GETDATE(), 23)
							update @TB set Pay_Price=@PriceMonth*@Term
							update @TB set EndDate=@d2 where EndDate>@d2
							update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
							update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
							update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
							--update @TB set HF_Date = null where id = 1
							update @TB set Get_Price2 = -Pay_Price2 
							insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate
							,Pay_Price,Pay_Price2,Get_Price2
							,CreateUser,updatetime)
							select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
							,Pay_Price,Pay_Price2,Get_Price2,@LogName,updatee
							from @TB a
							order by a.id
							
							Insert into OA_LeaseContract_ExMarked values (1,GETDATE()) 
							update OA_LeaseContract_Finance set Datelines = GETDATE()
							update OA_LeaseContract_ExMarked set run_date=CONVERT(varchar(10), GETDATE(), 23)
			        End
   End
   
GO


加载更多回复(7)

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧