执行存储过程报这种错怎么解决
在WEB系统中执行了一个存储地过程,在存储过程中我建有一临时表,在过程的最后我显示的删除了此临时表,但是报这种错:
There was a transaction active when exiting the stored procedure 'up_RechangePrice'. The temporary table '#__temp1' was dropped in this transaction either explicitly or implicitly. This transaction has been aborted to prevent database corruption.
那位知道是什么原因,怎么解决?(即使把删除临时表的那个语句去掉还是后一样的错)
存储过程如下:
create procedure up_RechangePrice
@PaperNO varchar(16),
@UserNO varchar(20),
@LastUpdateTime datetime
as
begin
create table #__temp1( sStoreNO varchar(12))
declare cursor_store cursor for select sStoreNO from tPriceStore where sPaperNO =@PaperNO
open cursor_store
declare @storeTypeID varchar(2)
declare @storeNO varchar(12)
fetch cursor_store into @storeNO
while (@@sqlstatus=0)
begin
select @storeTypeID = sStoreTypeID from tStore where sStoreNO = @storeNO
if @storeTypeID = '0'
begin
insert into #__temp1
select sStoreNO from tStore where sStoreTypeID = '1'
end
else if @storeTypeID = '3'
begin
insert into #__temp1
select sStoreNO from tStore where sZoneNO=@storeNO and sStoreTypeID = '1'
end
else if @storeTypeID = '1'
begin
insert into #__temp1 values(@storeNO)
end
fetch cursor_store into @storeNO
end
close cursor_store
deallocate cursor cursor_store
insert into tSalePriceRechange(sStoreNO,sPaperNO,nItem,sPRSTypeID,sPRSType,nGoodsID,
dSaleBeginDate,dSaleEndDate,nNewSalePrice,nTag, sCreateUser,
dCreateDate, dLastUpdateTime)
select distinct c.sStoreNO,a.sPaperNO,b.nItem,a.sPRSTypeID,a.sPRSType,b.nGoodsID,
a.dSaleBeginDate,a.dSaleEndDate,b.nNewSalePrice,0,@UserNO,
@LastUpdateTime,@LastUpdateTime
from tPrice a,tPriceDtl b,#__temp1 c
where a.sPaperNO = b.sPaperNO
and a.sPaperNO = @PaperNO
drop table #__temp1
end