首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

这个有关问题真想不通,有关存储过程中调用并更改视图,但不能实现

2012-03-01 
这个问题真想不通,有关存储过程中调用并更改视图,但不能实现这个问题真想不通,有关存储过程中调用并更改视

这个问题真想不通,有关存储过程中调用并更改视图,但不能实现
这个问题真想不通,有关存储过程中调用并更改视图,但不能实现

问题介绍:
用如下的过程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 ')

热点排行