--用游标就可以了.CREATE PROCEDURE order_tot_amt
@o_id int
AS
declare @pro_names varchar(100)
declare @pro_oldcount int
declare @pro_newcount int
declare @procount int
declare @SQL nvarchar (2000)declare tb cursor local for
SELECT pro_name,pro_count
FROM loogoo_goods WHERE order_id=@o_id
open tb
fetch next from tb into @pro_names,@procount
while @@fetch_status=0
begin
SELECT @pro_oldcount=counts from ys_shop where ys_name=@pro_names
select @pro_newcount = @pro_oldcount-@procount
if @pro_newcount >1
update ys_shop set counts=@pro_newcount where ys_name =@pro_names
fetch next from tb into @pro_names,@procount
end
close tb
deallocate tb
GO
@o_id int
AS
declare @pro_names varchar(100)
declare @pro_oldcount int
declare @pro_newcount int
declare @procount int
declare @SQL nvarchar (2000)declare tb cursor local for
SELECT pro_name,pro_count
FROM loogoo_goods WHERE order_id=@o_id
open tb
fetch next from tb into @pro_names,@procount
while @@fetch_status=0
begin
SELECT @pro_oldcount=counts from ys_shop where ys_name=@pro_names
select @pro_newcount = @pro_oldcount-@procount
if @pro_newcount >1
update ys_shop set counts=@pro_newcount where ys_name =@pro_names
fetch next from tb into @pro_names,@procount
end
close tb
deallocate tb
GO
可是运行时候 报错了,
Microsoft OLE DB Provider for ODBC Drivers 错误 '80004005' [Microsoft][ODBC SQL Server Driver][SQL Server]ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION。 /shop_ys/sendfriend.asp,行29
CREATE PROCEDURE order_tot_amt
@o_id int
AS
declare @pro_names varchar(100)
declare @pro_oldcount int
declare @pro_newcount int
declare @procount int
declare @SQL nvarchar (2000)begin trandeclare tb cursor local for
SELECT pro_name,pro_count
FROM loogoo_goods WHERE order_id=@o_id
open tb
fetch next from tb into @pro_names,@procount
while @@fetch_status=0
begin
SELECT @pro_oldcount=counts from ys_shop where ys_name=@pro_names
select @pro_newcount = @pro_oldcount-@procount
if @pro_newcount >1
update ys_shop set counts=@pro_newcount where ys_name =@pro_names
else
return(2)
fetch next from tb into @pro_names,@procount
end
close tb
deallocate tb
update loogoo_order set isend=1 where id=@o_id
if @@error<>0 goto err
commit tran
err:
rollback tran
GO
@o_id int
AS
declare @pro_names varchar(100)
declare @pro_oldcount int
declare @pro_newcount int
declare @procount int
declare @SQL nvarchar (2000)begin trandeclare tb cursor local for
SELECT pro_name,pro_count
FROM loogoo_goods WHERE order_id=@o_id
open tb
fetch next from tb into @pro_names,@procount
while @@fetch_status=0
begin
SELECT @pro_oldcount=counts from ys_shop where ys_name=@pro_names
select @pro_newcount = @pro_oldcount-@procount
if @pro_newcount >1
update ys_shop set counts=@pro_newcount where ys_name =@pro_names
else
return(2)
fetch next from tb into @pro_names,@procount
end
close tb
deallocate tb
update loogoo_order set isend=1 where id=@o_id
if @@error<>0
rollback tran
else
commit tran
GO
@o_id int
AS
declare @pro_names varchar(100)
declare @pro_oldcount int
declare @pro_newcount int
declare @procount int
declare @SQL nvarchar (2000)declare @re intbegin trandeclare tb cursor local for
SELECT pro_name,pro_count
FROM loogoo_goods WHERE order_id=@o_id
open tb
fetch next from tb into @pro_names,@procount
while @@fetch_status=0
begin
SELECT @pro_oldcount=counts from ys_shop where ys_name=@pro_names
select @pro_newcount = @pro_oldcount-@procount
if @pro_newcount >1
update ys_shop set counts=@pro_newcount where ys_name =@pro_names
else
begin
set @re=2
goto lb_exit
end
--return(2) --这个是干什么的?
fetch next from tb into @pro_names,@procount
end
close tb
deallocate tb
update loogoo_order set isend=1 where id=@o_id
if @@error<>0
rollback tran
else
commit tran
returnlb_exit:
rollback tran
return(@re)
GO