22,300
社区成员
发帖
与我相关
我的任务
分享sql中如何加判断，让这个存储过程只执行一次，再次点击不要重复插入数据
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
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
...全文
请发表友善的回复…
发表回复
程序猿老曾 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
- 打赏
- 举报
这条语句有没有插入成功 ?
Insert into OA_LeaseContract_ExMarked values (1,GETDATE()) [/quote]
有记录到，时间还是带秒的，如上图
顺势而为1 2017-05-27
- 打赏
- 举报
这条语句有没有插入成功 ?
Insert into OA_LeaseContract_ExMarked values (1,GETDATE())
顺势而为1 2017-05-27
- 打赏
- 举报
OA_LeaseContract_ExMarked 表中有没有记录到数据 ?
luckychap 2017-05-27
- 打赏
- 举报
嗯。。关键是出错啊。。没有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
顺势而为1 2017-05-27
- 打赏
- 举报
嗯。。关键是出错啊。。没有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
顺势而为1 2017-05-27
- 打赏
- 举报
嗯。。关键是出错啊。。没有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
- 打赏
- 举报
嗯。。关键是出错啊。。没有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
- 打赏
- 举报
执行不出来了。。[/quote]
那这就说明没有执行下面这些代码, 程序没问题啊.
[/quote]
只是不知你这显示的空结果集是怎么来的 ?[/quote]
这个空结果，
exec OA_LeaseContract_Finance_Proce_Init 100011,'asd'
select * from OA_LeaseContract_Finance
是最底下这句，我没截到图，您可能没看到，最底下有这句顺势而为1 2017-05-27
- 打赏
- 举报
执行不出来了。。[/quote]
那这就说明没有执行下面这些代码, 程序没问题啊.
[/quote]
只是不知你这显示的空结果集是怎么来的 ?顺势而为1 2017-05-27
- 打赏
- 举报
执行不出来了。。[/quote]
那这就说明没有执行下面这些代码, 程序没问题啊.
luckychap 2017-05-27
- 打赏
- 举报
执行不出来了。。
顺势而为1 2017-05-27
- 打赏
- 举报
1. 那没道理啊, 怎么看你的贴图, 少了Begin ? , 或者在 Begin 下面再测一下@run_flag的值
IF @run_flag=0 --今日未执行
Begin
Select @run_flag
luckychap 2017-05-27
- 打赏
- 举报
还是可以重复执行。。好郁闷[/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
- 打赏
- 举报
还是可以重复执行。。好郁闷[/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
- 打赏
- 举报
还是可以重复执行。。好郁闷[/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
- 打赏
- 举报
还是可以重复执行。。好郁闷
顺势而为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）
