这个问题真想不通,有关存储过程中调用并更改视图,但不能实现
这个问题真想不通,有关存储过程中调用并更改视图,但不能实现
问题介绍:
用如下的过程create procedure dxyvxa_update_mk_qty_rate来工作,在执行过程中想要更改两个视图的定义,但不能成功操作,提示是 "alter view "这个语句必须要是过程的第一行,有点不解,我在软件中是可以用单独的语句,然后用立即执行可以实现,但现在改为过程却不能。想过用EXECUTE 加字符串来操作,但长度超过了128位,不能成功动行。
基于以上问题,有什么好的实现方法或别的方法实现?请赐教。
create procedure dxyvxa_update_mk_qty_rate
@mk_no varchar(24)
as
declare @str1 as varchar(8000)
delete from dxyvxa_ps_ctrl_have_bad_qty where mk_no =@mk_no
alter view dxyvxa_view_mk_bad_purin_qty
with encryption
as
select mk_no, part_no,sum(abs(pout_qty)) bad_purin from p_ps_out2
where substring(pout_no,1,2)= 'D2 '
and pout_no in (select pout_no from p_ps_out1 where st_ok = 'Y ' and mk_no = @mk_no )
group by part_no,mk_no
alter view dxyvxa_view_mk_bad_make_qty
with encryption
as
select mk_no, part_no,sum(abs(pout_qty)) bad_make
from p_ps_out2
where substring(pout_no,1,2)= 'E1 '
and pout_no in (select pout_no from p_ps_out1 where st_ok = 'Y ' and mk_no = @mk_no)
group by part_no,mk_no
insert into dxyvxa_ps_ctrl_have_bad_qty
select mk_no, part_no,mpart_no,status,lot,yl_qty,ll_qty,0,0,0
from ps_ctrl
where mk_no = @mk_no
update dxyvxa_ps_ctrl_have_bad_qty
set dxyvxa_ps_ctrl_have_bad_qty.sum_ll_qty = dxyvxa_view_mk_llqty.sum_ll_qty
from dxyvxa_view_mk_llqty
where dxyvxa_ps_ctrl_have_bad_qty.mk_no = @mk_no
and dxyvxa_ps_ctrl_have_bad_qty.part_no = dxyvxa_view_mk_llqty.part_no
update dxyvxa_ps_ctrl_have_bad_qty
set dxyvxa_ps_ctrl_have_bad_qty.bad_purin = dxyvxa_view_mk_bad_purin_qty.bad_purin
from dxyvxa_view_mk_bad_purin_qty
where dxyvxa_ps_ctrl_have_bad_qty.mk_no = @mk_no
and dxyvxa_ps_ctrl_have_bad_qty.part_no = dxyvxa_view_mk_bad_purin_qty.part_no
update dxyvxa_ps_ctrl_have_bad_qty
set dxyvxa_ps_ctrl_have_bad_qty.bad_make = dxyvxa_view_mk_bad_make_qty.bad_make
from dxyvxa_view_mk_bad_make_qty
where dxyvxa_ps_ctrl_have_bad_qty.mk_no = @mk_no
and dxyvxa_ps_ctrl_have_bad_qty.part_no = dxyvxa_view_mk_bad_make_qty.part_no
[解决办法]
修改视图为什么一定要在存储过程里面去实现
[解决办法]
改成如下,请试一下:
create procedure dxyvxa_update_mk_qty_rate
@mk_no varchar(24)
as
declare @str1 as varchar(8000)
delete from dxyvxa_ps_ctrl_have_bad_qty where mk_no =@mk_no
exec( '
alter view dxyvxa_view_mk_bad_purin_qty
with encryption
as
select mk_no, part_no,sum(abs(pout_qty)) bad_purin from p_ps_out2
where substring(pout_no,1,2)= '+ 'D2 '+ '
and pout_no in (select pout_no from p_ps_out1 where st_ok = '+ 'Y '+ ' and mk_no = '+@mk_no+ ' )
group by part_no,mk_no
alter view dxyvxa_view_mk_bad_make_qty
with encryption
as
select mk_no, part_no,sum(abs(pout_qty)) bad_make
from p_ps_out2
where substring(pout_no,1,2)= '+ 'E1 '+ '
and pout_no in (select pout_no from p_ps_out1 where st_ok = '+ 'Y '+ ' and mk_no = '+@mk_no+ ')
group by part_no,mk_no
insert into dxyvxa_ps_ctrl_have_bad_qty
select mk_no, part_no,mpart_no,status,lot,yl_qty,ll_qty,0,0,0
from ps_ctrl
where mk_no = '+@mk_no+ '
update dxyvxa_ps_ctrl_have_bad_qty
set dxyvxa_ps_ctrl_have_bad_qty.sum_ll_qty = dxyvxa_view_mk_llqty.sum_ll_qty
from dxyvxa_view_mk_llqty
where dxyvxa_ps_ctrl_have_bad_qty.mk_no = '+@mk_no+ '
and dxyvxa_ps_ctrl_have_bad_qty.part_no = dxyvxa_view_mk_llqty.part_no
update dxyvxa_ps_ctrl_have_bad_qty
set dxyvxa_ps_ctrl_have_bad_qty.bad_purin = dxyvxa_view_mk_bad_purin_qty.bad_purin
from dxyvxa_view_mk_bad_purin_qty
where dxyvxa_ps_ctrl_have_bad_qty.mk_no = '+@mk_no+ '
and dxyvxa_ps_ctrl_have_bad_qty.part_no = dxyvxa_view_mk_bad_purin_qty.part_no
update dxyvxa_ps_ctrl_have_bad_qty
set dxyvxa_ps_ctrl_have_bad_qty.bad_make = dxyvxa_view_mk_bad_make_qty.bad_make
from dxyvxa_view_mk_bad_make_qty
where dxyvxa_ps_ctrl_have_bad_qty.mk_no = '+@mk_no+ '
and dxyvxa_ps_ctrl_have_bad_qty.part_no = dxyvxa_view_mk_bad_make_qty.part_no ')