CREATE PROCEDURE jz
@ypmc varchar(50),
@ypsl varchar(50)
AS
DECLARE ci CURSOR
KEYSET
FOR select 处方.剂数,药品总库.整价 ,处方药品.药品数量 from 处方 , 药品总库, 处方药品
where exists
(select 处方药品.处方名称 from 处方药品 , 处方
where 处方药品.处方编号=处方.处方编号)
if(药品数量>0)
set xact_abort on
begin tran
update 药品总库 set 总量=总量-剂数
where 药品名称=@ypmc
commit tran
DECLARE @yj int,@qf int,@js int,@zj int
open ci
FETCH NEXT FROM ci INTO@yj,@qf,@js,@zj
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN set @yj=@js*','*@zj
END
FETCH NEXT FROM ci INTO @js,@zj
END
CLOSE ci
DEALLOCATE ci
GO
@ypmc varchar(50),
@ypsl varchar(50)
AS
DECLARE ci CURSOR
KEYSET
FOR select 处方.剂数,药品总库.整价 ,处方药品.药品数量 from 处方 , 药品总库, 处方药品
where exists
(select 处方药品.处方名称 from 处方药品 , 处方
where 处方药品.处方编号=处方.处方编号)
if(药品数量>0)
set xact_abort on
begin tran
update 药品总库 set 总量=总量-剂数
where 药品名称=@ypmc
commit tran
DECLARE @yj int,@qf int,@js int,@zj int
open ci
FETCH NEXT FROM ci INTO@yj,@qf,@js,@zj
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN set @yj=@js*','*@zj
END
FETCH NEXT FROM ci INTO @js,@zj
END
CLOSE ci
DEALLOCATE ci
GO
中变量的顺序要和定义游标时select后面的列顺序一致。
FETCH NEXT FROM ci INTO @yj,@qf,@js,@zj
lz少写个空格...