SQL Server transaction+在sql执行中进行条件判断

今天药忘吃喽~ 2021-12-05 19:09 278阅读 0赞

USE [IPPower]
    GO
    /****** Object:  StoredProcedure [dbo].[proc_AddHistoricalConsumption]    Script Date: 2019/7/17 14:19:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[proc_AddHistoricalConsumption]
    @PowerDeviceID int,        --设备ID
    @PowerPortID int,          --端口ID
    @DayJoule float        --设备ID
    as
    begin transaction
    
    declare @isExit int
    declare @olddayJoule float 
    
    select @isExit = count(ID) from P_HistoricalConsumption where PowerDeviceID = @PowerDeviceID and PowerPortID = @PowerPortID and DayTime = CONVERT(varchar(100), GETDATE(), 23)
    
    if @isExit <> 0 
    
    begin
    select @olddayJoule = DayJoule from P_HistoricalConsumption where PowerDeviceID = @PowerDeviceID and PowerPortID = @PowerPortID and DayTime = CONVERT(varchar(100), GETDATE(), 23)
    update P_HistoricalConsumption set DayJoule= (@olddayJoule + @DayJoule), ModifyDate = GETDATE() where PowerDeviceID = @PowerDeviceID and PowerPortID = @PowerPortID and DayTime = CONVERT(varchar(100), GETDATE(), 23) 
    end 
    
    else
    
    begin 
    insert into P_HistoricalConsumption (PowerDeviceID,PowerPortID,DayJoule,Year,Month,Day,DayTime,DeleteMark,CreateDate) values (@PowerDeviceID,@PowerPortID,@DayJoule,datename(year,GETDATE()),datename(Month,GETDATE()),datename(day,GETDATE()),GETDATE(),0, GETDATE()) 
    end
    
    commit transaction

begin transaction

declare @parameter  type

select @parameter  = count(\*) from table\_name where A = ' '

if @parameter1 <> 0

begin

执行sql

end

else

begin

执行sql

end

commit transaction