sql中如何加判断，让这个存储过程执行一次之后，重复点击，不会重复执行

[luckychap]

USE CuService
GO
alter Proc dbo.OA_LeaseContract_Finance_Proce_Init
@LTID int,
@LogName varchar(30)
as
IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
Begin
declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
ALTER TABLE [dbo].[OA_LeaseContract_Finance] Drop [OA_LeaseContract_Finance_LFID]
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)
values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3)
set @dtmp=dateadd(m,@Term,@dtmp)
--insert into @TB(Awakeday)
--values(@dtmp-@d3)
--Select case when Advanceday > 0
--then Select id,DATEADD(day,-Advanceday,BeginDate) end from @TB
End

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)
select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate
,Pay_Price,Pay_Price2,Get_Price2,@LogName
from @TB a
order by a.id

End

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

exec OA_LeaseContract_Finance_Proce_Init 100001,'asd'

select * from OA_LeaseContract_Finance
这其中在AS下面那个IF判断的代码，写错了，执行不了，请教该如何修改？

[顺势而为1]

你贴出来的代码少了个Begin

USE CuService
GO
alter Proc dbo.OA_LeaseContract_Finance_Proce_Init
@LTID int,
@LogName varchar(30)
as

Begin
    IF  EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
              Begin
						declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
								ALTER TABLE [dbo].[OA_LeaseContract_Finance] Drop  [OA_LeaseContract_Finance_LFID]
						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)
						values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3)
						set @dtmp=dateadd(m,@Term,@dtmp)
						--insert into @TB(Awakeday)	
						--values(@dtmp-@d3)
						--Select case when Advanceday > 0
						--then Select id,DATEADD(day,-Advanceday,BeginDate) end from @TB	
               End

	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)
    select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate,Pay_Price,Pay_Price2,Get_Price2,@LogName
    from @TB a
    order by a.id
End



GO

[顺势而为1]

改后

USE CuService
GO
alter Proc dbo.OA_LeaseContract_Finance_Proce_Init
@LTID int,
@LogName varchar(30)
as

Begin

	DECLARE @run_flag int
	SET @run_flag=0
	IF EXISTS(Select 1 From cRunFlagTbl where run_date=getdate() and run_flag=1)
	        SET @run_flag=1
	
	IF @run_flag=0   --今日未执行
		BEGIN

				IF  EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
						  Begin
									declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
											ALTER TABLE [dbo].[OA_LeaseContract_Finance] Drop  [OA_LeaseContract_Finance_LFID]
									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)
									values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3)
									set @dtmp=dateadd(m,@Term,@dtmp)
									--insert into @TB(Awakeday)	
									--values(@dtmp-@d3)
									--Select case when Advanceday > 0
									--then Select id,DATEADD(day,-Advanceday,BeginDate) end from @TB	
						   End

				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)
				select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate,Pay_Price,Pay_Price2,Get_Price2,@LogName
				from @TB a
				order by a.id
				
				
				--run 完后加入标记
				Insert into cRunFlagTbl values (GETDATE(),1) 
				
				
	   END
End



GO

[中国风]

删除约束这一段加if判断，不加重复执行时会报错

IF EXISTS(SELECT * FROM sys.objects WHERE parent_object_id=OBJECT_ID('dbo.[OA_LeaseContract_Finance]') AND name='OA_LeaseContract_Finance_LFID')
        ALTER TABLE [dbo].[OA_LeaseContract_Finance] Drop  [OA_LeaseContract_Finance_LFID]

[顺势而为1]

getdate()需要转化成标准日期 , 比如 2017-05-25 15:14:21.167 要转成 2017-05-25 , 要不然取不到值

[顺势而为1]

引用 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