34,576
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[sp_DataComputer_WenYuanDatas]
@DANo CHAR(32) ,--20110112182345207插入时间
@DATime DATETIME ,--采集时间
@LogTime DATETIME ,--更新时间
@MeterType CHAR(4) ,--表具类型
@MeterNo CHAR(20) ,--表具号码
@Qty DECIMAL(18, 6) ,--数值
@Unit CHAR(4) ,--单位
@outputpar INT = 0 OUTPUT--输出返回值
AS --带事务存储过程模板
BEGIN
--开始事务
BEGIN TRANSACTION
SAVE TRANSACTION sp_Datacomputer_TRANS
--事务内容,计算时日月数据.
DECLARE @DAYear CHAR(4);
DECLARE @DAMonth CHAR(2);
DECLARE @DADay CHAR(2);
DECLARE @DAHour CHAR(2);
DECLARE @RowCnt_Year INT;
DECLARE @RowCnt_Month INT;
DECLARE @RowCnt_Day INT;
DECLARE @RowCnt_Hour INT;
SET @DAYear = SUBSTRING(LTRIM(RTRIM(CONVERT(VARCHAR(10), @DATime, 120))),
1, 4);
SET @DAMonth = SUBSTRING(LTRIM(RTRIM(CONVERT(VARCHAR(10), @DATime, 120))),
6, 2);
SET @DADay = SUBSTRING(LTRIM(RTRIM(CONVERT(VARCHAR(10), @DATime, 120))),
9, 2);
SET @DAHour = SUBSTRING(LTRIM(RTRIM(CONVERT(VARCHAR(10), @DATime, 114))),
1, 2);
SET @RowCnt_Year = ( SELECT COUNT(*)
FROM WenYuanEnergyDataSumByYear
WHERE MeterNo = @MeterNo
AND MeterType = @MeterType
AND DAYear = @DAYear
);
IF ISNULL(@RowCnt_Year, 0) = 0
BEGIN
SET @RowCnt_Year = 0;
END
IF @RowCnt_Year > 0
BEGIN
UPDATE WenYuanEnergyDataSumByYear
SET SumQty = SumQty + @Qty
WHERE MeterNo = @MeterNo
AND MeterType = @MeterType
AND DAYear = @DAYear;
END
ELSE
BEGIN
INSERT WenYuanEnergyDataSumByYear
( MeterNo ,
MeterType ,
DAYear ,
SumQty ,
Unit
)
SELECT @MeterNo ,
@MeterType ,
@DAYear ,
@Qty ,
@Unit;
END
----------
--发生错误回滚事务
IF ( @@error <> 0 )
BEGIN
GOTO LABROLLBACK
END
--提交事务
LABCOMMIT:
IF ( @@error = 0 )
BEGIN
COMMIT TRANSACTION
RETURN(0)
END
--以下回滚事务
LABROLLBACK:
BEGIN
ROLLBACK TRANSACTION sp_Datacomputer_TRANS
RETURN @@error
END
END