各位大哥:
我有三个表:
1:TemporaryStoreroomTable //临时新增表 动态的创建
2:StoreroomTable//保存所有 临时新增表的数据
(orderid varchar(50),
masterid varchar(50),
mastername varchar(100),
numbers int,
times datetime
PRIMARY KEY(IncomeStoreroomId,OperateTime),
)
3:AllStoreroomTable//总数量表
(masterid varchar(50),
numbers int,
)
和两个存储过程://**********每次需要新增数据时,便创建此表,把需要插入的数据 先保存到此表CREATE procedure ProceCreateTemporaryStoreroomFormTable as
if Exists(select 1 from dbo.sysobjects where xtype = 'U' and name = 'TemporaryStoreroomTable')
begin
drop table TemporaryStoreroomTable
end
create table TemporaryIncomeStoreroomFormTable
( orderid varchar(50),
masterid varchar(50),
mastername varchar(100),
numbers int,
times datetime
PRIMARY KEY(IncomeStoreroomId,OperateTime),
)GO//**********每次需要新增数据时,便创建此表,把需要插入的数据 先保存到此表//如果 TemporaryStoreroomTable 表的数据需要真正的提交才运行此存储过程CREATE procedure ProcInsertStoreroomTable
as
declare @orderid varchar(50),
@masterid varchar(50),
@mastername varchar(100),
@numbers int,
@times datetime
declare @InsertIncomeStoreroom_Cursor CURSOR begin
begin tran
insert StoreroomTable(orderid,masterid,mastername,numbers,times)
select orderid,masterid,mastername,numbers,times
from TemporaryStoreroomTable if @@error<>0
begin
raiserror('操作不成功',16,1)
rollback tran
return
end
SET @InsertIncomeStoreroom_Cursor=CURSOR SCROLL DYNAMIC
FOR
SELECT orderid,masterid,mastername,numbers,times
FROM TemporaryStoreroomTable
OPEN @InsertIncomeStoreroom_Cursor
FETCH NEXT FROM @InsertIncomeStoreroom_Cursor INTO @orderid,@masterid,@mastername,@numbers,@times
WHILE @@FETCH_STATUS=0 BEGIN
if exists(select * from AllStoreroomTable where masterid=@masterid)
begin
update AllStoreroomTable
set numbers=numbers+@ numbers
where masterid=@masterid
if @@error<>0 or @@rowcount<>1
begin
raiserror('操作不成功',16,1)
rollback tran
return
end
end
else
begin
insert AllStoreroomTable(masterid,masterid)
values (@masterid,@masterid)
if @@error<>0 or @@rowcount<>1
begin
raiserror('操作不成功',16,1)
rollback tran
return
end
end
end
FETCH NEXT FROM @InsertIncomeStoreroom_Cursor INTO @orderid,@masterid,@mastername,@numbers,@times
end CLOSE @InsertIncomeStoreroom_Cursor
DEALLOCATE @InsertIncomeStoreroom_Cursor
if @@error=0
commit tran
else
rollback tran
endGO//如果 TemporaryStoreroomTable 表的数据需要真正的提交才运行此存储过程
各位大哥,现在的问题是,ProcInsertStoreroomTable 我用到了游标,他们说游标的速度会很慢(我不知道是不是真的),各位大哥,是不是这样啊?还有可以不通过游标,
利用别的方法可以实现吗?
谢谢 各位大哥!
我有三个表:
1:TemporaryStoreroomTable //临时新增表 动态的创建
2:StoreroomTable//保存所有 临时新增表的数据
(orderid varchar(50),
masterid varchar(50),
mastername varchar(100),
numbers int,
times datetime
PRIMARY KEY(IncomeStoreroomId,OperateTime),
)
3:AllStoreroomTable//总数量表
(masterid varchar(50),
numbers int,
)
和两个存储过程://**********每次需要新增数据时,便创建此表,把需要插入的数据 先保存到此表CREATE procedure ProceCreateTemporaryStoreroomFormTable as
if Exists(select 1 from dbo.sysobjects where xtype = 'U' and name = 'TemporaryStoreroomTable')
begin
drop table TemporaryStoreroomTable
end
create table TemporaryIncomeStoreroomFormTable
( orderid varchar(50),
masterid varchar(50),
mastername varchar(100),
numbers int,
times datetime
PRIMARY KEY(IncomeStoreroomId,OperateTime),
)GO//**********每次需要新增数据时,便创建此表,把需要插入的数据 先保存到此表//如果 TemporaryStoreroomTable 表的数据需要真正的提交才运行此存储过程CREATE procedure ProcInsertStoreroomTable
as
declare @orderid varchar(50),
@masterid varchar(50),
@mastername varchar(100),
@numbers int,
@times datetime
declare @InsertIncomeStoreroom_Cursor CURSOR begin
begin tran
insert StoreroomTable(orderid,masterid,mastername,numbers,times)
select orderid,masterid,mastername,numbers,times
from TemporaryStoreroomTable if @@error<>0
begin
raiserror('操作不成功',16,1)
rollback tran
return
end
SET @InsertIncomeStoreroom_Cursor=CURSOR SCROLL DYNAMIC
FOR
SELECT orderid,masterid,mastername,numbers,times
FROM TemporaryStoreroomTable
OPEN @InsertIncomeStoreroom_Cursor
FETCH NEXT FROM @InsertIncomeStoreroom_Cursor INTO @orderid,@masterid,@mastername,@numbers,@times
WHILE @@FETCH_STATUS=0 BEGIN
if exists(select * from AllStoreroomTable where masterid=@masterid)
begin
update AllStoreroomTable
set numbers=numbers+@ numbers
where masterid=@masterid
if @@error<>0 or @@rowcount<>1
begin
raiserror('操作不成功',16,1)
rollback tran
return
end
end
else
begin
insert AllStoreroomTable(masterid,masterid)
values (@masterid,@masterid)
if @@error<>0 or @@rowcount<>1
begin
raiserror('操作不成功',16,1)
rollback tran
return
end
end
end
FETCH NEXT FROM @InsertIncomeStoreroom_Cursor INTO @orderid,@masterid,@mastername,@numbers,@times
end CLOSE @InsertIncomeStoreroom_Cursor
DEALLOCATE @InsertIncomeStoreroom_Cursor
if @@error=0
commit tran
else
rollback tran
endGO//如果 TemporaryStoreroomTable 表的数据需要真正的提交才运行此存储过程
各位大哥,现在的问题是,ProcInsertStoreroomTable 我用到了游标,他们说游标的速度会很慢(我不知道是不是真的),各位大哥,是不是这样啊?还有可以不通过游标,
利用别的方法可以实现吗?
谢谢 各位大哥!
既然是提交后才更新,全程事务封装,把所有操作交给SQLSERVER就行了.
adoconnection1.CommitTrans;
adoconnection1.RollbackTrans;