疑难问题
【这个错误怎么办？】EXECUTE 后的事务计数指示缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。上一计数 = 1，当前计数 = 2
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区我的写了sql存储过程，出现这个错误，是怎么回事？麻烦大神给可能下！本人先谢了！
这个是报错的那段代码，应该怎么改呢？
这个是报错的那段代码，应该怎么改呢？
nextinsert:
--插入月结主表对应数据
insert into dbo.t_StockStorage
( c_ID ,
c_Type ,
c_JigouID ,
c_Month ,
c_StaffID ,
c_Ctime ,
c_Remark
)
VALUES ( @storid, -- c_ID - nvarchar(50)
'无毛绒' , -- c_Type - nvarchar(50)
@JigouID , -- c_JigouID - nvarchar(50)
@yjtime, -- c_Month - datetime
@staffid , -- c_StaffID - nvarchar(50)
@createtime , -- c_Ctime - datetime
@remark -- c_Remark - ntext
)
--获取插入到月结主表最新的主键id
select @storageId = scope_identity()
/*计算库存月结数据逻辑SQL语句，可以利用临时表或with cte的用法，下文以cte为主。*/
--向月结子表插入数据
--insert into StockStorageDetail
--select @storageId,productid,quantity
--from cte
IF EXISTS(SELECT 1 FROM dbo.t_StockStorage WHERE c_JigouID=@JigouID)
/*第一种情况：从未做过月结*/
BEGIN
SELECT * INTO #t_inv FROM dbo.t_CashmereInventory WHERE c_Pihao IN (SELECT c_Pihao FROM dbo.v_CashmereInBodyDetails WHERE c_time<=@yjtime AND c_jigouID=@JigouID AND IsAccount=1)
UPDATE #t_inv SET c_Inventory=0 --将数据归零准备重新计算
DECLARE yb_ph CURSOR FOR
SELECT c_Pihao,c_ChangduID,c_PositionID FROM #t_inv WHERE c_JiGouID=@JigouID;
SELECT @count = count(*) FROM #t_inv WHERE c_JiGouID=@jigouid;
OPEN yb_ph
WHILE @count >0 AND @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM yb_ph INTO @pihao,@changdu,@kuwei
/*先取入库数据进行运算*/
SELECT @rksj =c_Weight FROM dbo.v_CashmereInBodyDetails WHERE c_Pihao=@pihao AND LengthID=@changdu AND c_PositionID=@kuwei AND c_jigouID=@JigouID
UPDATE #t_inv SET c_Inventory=c_Inventory+@rksj WHERE c_Pihao=@pihao AND c_ChangduID=@changdu AND c_PositionID=@kuwei AND c_jigouID=@JigouID
/*再取出库数据进行运算*/
SELECT @cksj =c_Weight FROM dbo.v_CashmereOutDetails WHERE c_Pihao=@pihao AND LenthID=@changdu AND c_PositionID=@kuwei AND c_jigouID=@JigouID
UPDATE #t_inv SET c_Inventory=c_Inventory-@cksj WHERE c_Pihao=@pihao AND c_ChangduID=@changdu AND c_PositionID=@kuwei AND c_jigouID=@JigouID
/*将数据插入到月结明细表中*/
INSERT INTO dbo.t_StockStorageDetail
( c_StorageId ,
c_Pihao ,
c_Pinming ,
c_ChangduID ,
c_Inventory ,
c_Remark ,
c_PositionID ,
c_JiGouID ,
c_Bianma ,
c_Xidu ,
c_Hancu ,
c_Yise ,
c_PType ,
c_Chundu ,
c_Yizhi ,
c_Fupi ,
c_Duanrong
)
SELECT @storid,
c_Pihao ,
c_Pinming ,
c_ChangduID ,
c_Inventory ,
c_Remark ,
c_PositionID ,
c_JiGouID ,
c_Bianma ,
c_Xidu ,
c_Hancu ,
c_Yise ,
c_PType ,
c_Chundu ,
c_Yizhi ,
c_Fupi ,
c_Duanrong
FROM #t_inv
END
END
ELSE
/*已经有月结记录，本期库存=期初库存+入库-出库*/
SELECT * INTO #t_inv0 FROM dbo.t_CashmereInventory WHERE 1>1
--从月结表中先将上月期初数据复制过来
INSERT INTO #t_inv0
( c_Pihao ,
c_Pinming ,
c_ChangduID ,
c_Inventory ,
c_Remark ,
c_PositionID ,
c_JiGouID ,
c_Bianma ,
c_Xidu ,
c_Hancu ,
c_Yise ,
c_PType ,
c_Chundu ,
c_Yizhi ,
c_Fupi ,
c_Duanrong
)
SELECT
c_Pihao ,
c_Pinming ,
c_ChangduID ,
c_Inventory ,
c_Remark ,
c_PositionID ,
c_JiGouID ,
c_Bianma ,
c_Xidu ,
c_Hancu ,
c_Yise ,
c_PType ,
c_Chundu ,
c_Yizhi ,
c_Fupi ,
c_Duanrong
FROM dbo.t_StockStorageDetail WHERE c_StorageId IN(SELECT c_ID FROM dbo.t_StockStorage WHERE c_Month=DATEADD(MONTH,-1,@yjtime) AND c_JigouID=@JigouID)
/*从库存表中复制上月新增批号的数据*/
DECLARE @yjPrev DATETIME ---上一个月结时间
DECLARE @InvID VARCHAR(50) --保存库存ID
DECLARE @pihao_n VARCHAR(50) --保存游标批号
DECLARE @changdu_n VARCHAR(50) --保存游标长度
DECLARE @kuwei_n VARCHAR(50) --保存游标库位
SELECT @yjPrev=c_Month FROM dbo.t_StockStorage WHERE c_Month=DATEADD(MONTH,-1,@yjtime) AND c_JigouID=@JigouID
DECLARE yb_phmuti CURSOR FOR
SELECT c_Pihao,LengthID,c_PositionID FROM dbo.v_CashmereInBodyDetails WHERE c_jigouID=@jigouid AND c_time<=@yjtime AND c_time>=@yjPrev AND c_jigouID=@JigouID AND IsAccount=1 ;
SELECT @count = count(*) FROM #t_inv0 WHERE c_JiGouID=@JigouID AND c_time<=@yjtime AND c_time>=@yjPrev AND c_jigouID=@JigouID AND IsAccount=1;
OPEN yb_phmuti;
WHILE @count > 0 AND @@FETCH_STATUS=0
/*存储游标循环*/
BEGIN
FETCH NEXT FROM yb_phmuti INTO @pihao_n,@changdu_n,@kuwei_n;
IF NOT EXISTS(SELECT 1 FROM #t_inv0 WHERE c_Pihao=@pihao_n AND c_ChangduID=@changdu_n AND c_PositionID=@kuwei_n)
INSERT INTO #t_inv0
( c_Pihao ,
c_Pinming ,
c_ChangduID ,
c_Inventory ,
c_Remark ,
c_PositionID ,
c_JiGouID ,
c_Bianma ,
c_Xidu ,
c_Hancu ,
c_Yise ,
c_PType ,
c_Chundu ,
c_Yizhi ,
c_Fupi ,
c_Duanrong
)
SELECT
c_Pihao ,
c_Pinming ,
c_ChangduID ,
0 , --默认为0
c_Remark ,
c_PositionID ,
c_JiGouID ,
c_Bianma ,
c_Xidu ,
c_Hancu ,
c_Yise ,
c_PType ,
c_Chundu ,
c_Yizhi ,
c_Fupi ,
c_Duanrong
FROM dbo.t_CashmereInventory WHERE c_Pihao=@pihao_n AND c_PositionID=@kuwei_n AND c_ChangduID=@changdu_n AND c_JiGouID=@JigouID
END
/*循环结束*/
/*开始计算*/
DECLARE yb_phjs CURSOR FOR
SELECT c_Pihao,c_ChangduID,c_PositionID FROM #t_inv0;
SELECT @count = count(*) FROM #t_inv0;
OPEN yb_phjs
WHILE @count >0 AND @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM yb_phjs INTO @pihao,@changdu,@kuwei;
--计算入库数据
SELECT @rksj =c_Weight FROM dbo.v_CashmereInBodyDetails WHERE c_Pihao=@pihao AND LengthID=@changdu AND c_PositionID=@kuwei AND c_jigouID=@JigouID
UPDATE #t_inv0 SET c_Inventory=c_Inventory+@rksj WHERE c_Pihao=@pihao AND c_ChangduID=@changdu AND c_PositionID=@kuwei AND c_jigouID=@JigouID
--计算出库数据
SELECT @cksj =c_Weight FROM dbo.v_CashmereOutDetails WHERE c_Pihao=@pihao AND LenthID=@changdu AND c_PositionID=@kuwei AND c_jigouID=@JigouID
UPDATE #t_inv0 SET c_Inventory=c_Inventory-@cksj WHERE c_Pihao=@pihao AND c_ChangduID=@changdu AND c_PositionID=@kuwei AND c_jigouID=@JigouID
/*将数据插入到月结明细表中*/
INSERT INTO dbo.t_StockStorageDetail
( c_StorageId ,
c_Pihao ,
c_Pinming ,
c_ChangduID ,
c_Inventory ,
c_Remark ,
c_PositionID ,
c_JiGouID ,
c_Bianma ,
c_Xidu ,
c_Hancu ,
c_Yise ,
c_PType ,
c_Chundu ,
c_Yizhi ,
c_Fupi ,
c_Duanrong
)
SELECT @storid,
c_Pihao ,
c_Pinming ,
c_ChangduID ,
c_Inventory ,
c_Remark ,
c_PositionID ,
c_JiGouID ,
c_Bianma ,
c_Xidu ,
c_Hancu ,
c_Yise ,
c_PType ,
c_Chundu ,
c_Yizhi ,
c_Fupi ,
c_Duanrong
FROM #t_inv0
end
if(@@error<>0) goto Failure
commit transaction
set @text = @warehousename+replace(convert(varchar(7),@yjtime,120),'-','年')+'月月结数据插入已完成！'
return
...全文
相关推荐
C#事务处理（Execute Transaction）实例解析
如果在执行过程中出现错误，我们可以调用`Rollback()`方法来回滚事务，撤销所有操作，以保持数据的一致性。在实际应用中，事务处理通常用于更复杂的操作，例如涉及多个表的更新、删除或插入操作。
SQL SERVER事务处理详解.doc
M2;UPDATE table2; -- 更新表2COMMIT TRANSACTION M2;UPDATE table3; -- 更新表3COMMIT TRANSACTION T1;BEGIN DISTRIBUTED
浅析Python中MySQLdb的事务处理功能
以下是一个简单的示例：```pythontry: cursor = conn.cursor() cursor.execute(insertSQL1) cursor.execute(insertSQL2)
史上最全数据库SQL语句大全
事务控制语句用于管理事务的执行：***MIT：COMMIT语句用于结束当前事务，将事务中所有的改动永久保存到数据库。2. ROLLBACK：ROLLBACK语句用于中止当前事务，撤销事务中的所有操作。
SQL语句集锦SQL语句集锦
例如：`COMMIT;`#### ROLLBACK用于中止当前事务，撤销所有未提交的更改。例如：`ROLLBACK;`#### SET TRANSACTION用于定义当前事务的数据访问特征。
