***关于存储过程中游标使用的怪问题,请高手赐教，急急急！！！***

Question

不好意思，语句太乱，主要就是下面 两个地方】  set transaction isolation level serializable
  /*对bos_stk表进行加锁，copy期间不可以更新bos_stk表*/ ---就是这里当包含下面语句时 就不会执行return，存储过程的返回值为NULL 
insert bos_stk_bak(stk_date,stk_shop_id,stk_mod_id,stk_org_id,stk_col_id,stk_brand_id,stk_pici,stk_cost,stk_qty1,stk_qty2,stk_qty3,stk_qty4,stk_qty5,stk_qty6,stk_qty7,stk_qty8,stk_qty9,stk_qty10,stk_qty11,stk_qty12,stk_qty13,stk_qty14,stk_qty15,stk_qty16,stk_qty17)
values(@ddate,@cshop,@nmod,@corg,@ncol,@cbrand,@npici,@rcost,@nqty1,@nqty2,@nqty3,@nqty4,@nqty5,@nqty6,@nqty7,@nqty8,@nqty9,@nqty10,@nqty11,@nqty12,@nqty13,@nqty14,@nqty15,@nqty16,@nqty17)
-------------------------------------------------------------

Answer 1

你不用这么麻烦的！insert bos_stk_bak (stk_date,stk_shop_id,stk_mod_id,stk_org_id,stk_col_id,stk_brand_id,stk_pici,stk_cost,stk_qty1,stk_qty2,stk_qty3,stk_qty4,stk_qty5,stk_qty6,stk_qty7,stk_qty8,stk_qty9,stk_qty10,stk_qty11,stk_qty12,stk_qty13,stk_qty14,stk_qty15,stk_qty16,stk_qty17)
select stk_date,stk_shop_id,stk_mod_id,stk_org_id,stk_col_id,stk_brand_id,stk_pici,stk_cost,stk_qty1,stk_qty2,stk_qty3,stk_qty4,stk_qty5,stk_qty6,stk_qty7,stk_qty8,stk_qty9,stk_qty10,stk_qty11,stk_qty12,stk_qty13,stk_qty14,stk_qty15,stk_qty16,stk_qty17
from bos_stk order by stk_code
-----一句就可以了！

Answer 2

如果bos_stk的列全部都和bos_stk_bak的列相同：insert bos_stk_bak select * from bos_stk order by stk_code一句搞定

Answer 3

锁是没错啦。
用游标太麻烦啦，一句可以搞掂的。还可以用下 holdlock

Answer 4

CREATE PROCEDURE c_bakPd
AS
  set transaction isolation level serializable
  begin tran compile_all /*事务处理*/  insert into bos_stk_bak 
  select stk_date,stk_shop_id,stk_mod_id,stk_org_id,stk_col_id,stk_brand_id,stk_pici,stk_cost,stk_qty1,stk_qty2,stk_qty3,stk_qty4,stk_qty5,stk_qty6,stk_qty7,stk_qty8,stk_qty9,stk_qty10,stk_qty11,stk_qty12,stk_qty13,stk_qty14,stk_qty15,stk_qty16,stk_qty17 
  from bos_stk  commit tran compile_all      /*执行事务*/
  if @@error <> 0 GOTO E_Compile_Fail
  else
    return 1CleanUp:
  return 0
E_Compile_Fail:
  declare @lerror int
  select @lerror = @@error
  rollback tran compile_all
  RAISERROR (@lerror,16,-1)
  goto CleanUp
我也试过这种方式，关键是储存过程的return语句不执行，取不到返回值，有点奇怪！我去掉其中的插入语句，又有返回值，觉得这个问题有点怪！请指教！！！

Answer 5

commit tran compile_all      位置不对吧

Answer 6

位置对的，我去掉
insert into bos_stk_bak 
  select stk_date,stk_shop_id,stk_mod_id,stk_org_id,stk_col_id,stk_brand_id,stk_pici,stk_cost,stk_qty1,stk_qty2,stk_qty3,stk_qty4,stk_qty5,stk_qty6,stk_qty7,stk_qty8,stk_qty9,stk_qty10,stk_qty11,stk_qty12,stk_qty13,stk_qty14,stk_qty15,stk_qty16,stk_qty17 
  from bos_stk这段插入语句就没问题，所以奇怪！！！

Answer 7

又有一问：还请高手指教！！！  SET IDENTITY_INSERT bos_stk_cy On
  insert into bos_stk_cy
  select stk_date,stk_shop_id,pandian_mod_id,pandian_org_id,pandian_col_id,pandian_brand_id,pandian_cost,pandian_qty1-stk_qty1,pandian_qty2-stk_qty2,pandian_qty3-stk_qty3,pandian_qty4-stk_qty4,pandian_qty5-stk_qty5,pandian_qty6-stk_qty6,pandian_qty7-stk_qty7,pandian_qty8-stk_qty8,pandian_qty9-stk_qty9,pandian_qty10-stk_qty10,pandian_qty11-stk_qty11,pandian_qty12-stk_qty12,
pandian_qty13-stk_qty13,pandian_qty14-stk_qty14,pandian_qty15-stk_qty15,pandian_qty16-stk_qty16,pandian_qty17-stk_qty17 
  from  bos_pandian_det left join bos_stk
  on stk_mod_id=pandian_mod_id
  insert into bos_stk_cy
  select stk_date,stk_shop_id,stk_mod_id,stk_org_id,stk_col_id,stk_brand_id,stk_cost,0-stk_qty1,0-stk_qty2,0-stk_qty3,0-stk_qty4,0-stk_qty5,0-stk_qty6,0-stk_qty7, 0-stk_qty8,0-stk_qty9,0-stk_qty10,0-stk_qty11,0-stk_qty12,0-stk_qty13, 0-stk_qty14,0-stk_qty15,0-stk_qty16,0-stk_qty17
  from bos_stk
  where stk_mod_id not in (select pandian_mod_id from bos_pandian_det)
  SET IDENTITY_INSERT bos_stk_cy Off

Answer 8

上面这段代码想把将 bos_pandian_det 与 bos_stk表的差异值 存于 bos_stk_cy表中，注：bos_pandian_det,bos_stk,bos_stk_cy的表结构有点不一样，插入时报 仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 'bos_stk_cy' 中为标识列指定显式值。 出错信息！！但我已经加了SET IDENTITY_INSERT bos_stk_cy On 
                        SET IDENTITY_INSERT bos_stk_cy Off
语句。请指点！！！

Answer 9

SET IDENTITY_INSERT bos_stk_cy On
  insert into bos_stk_cy(把列写出来就不会错)
  select stk_date,stk_shop_id,pandian_mod_id,pandian_org_id,pandian_col_id,pandian_brand_id,pandian_cost,pandian_qty1-stk_qty1,pandian_qty2-stk_qty2,pandian_qty3-stk_qty3,pandian_qty4-stk_qty4,pandian_qty5-stk_qty5,pandian_qty6-stk_qty6,pandian_qty7-stk_qty7,pandian_qty8-stk_qty8,pandian_qty9-stk_qty9,pandian_qty10-stk_qty10,pandian_qty11-stk_qty11,pandian_qty12-stk_qty12,
pandian_qty13-stk_qty13,pandian_qty14-stk_qty14,pandian_qty15-stk_qty15,pandian_qty16-stk_qty16,pandian_qty17-stk_qty17 
  from  bos_pandian_det left join bos_stk
  on stk_mod_id=pandian_mod_id
  insert into bos_stk_cy (把列写出来就不会错)
  select stk_date,stk_shop_id,stk_mod_id,stk_org_id,stk_col_id,stk_brand_id,stk_cost,0-stk_qty1,0-stk_qty2,0-stk_qty3,0-stk_qty4,0-stk_qty5,0-stk_qty6,0-stk_qty7, 0-stk_qty8,0-stk_qty9,0-stk_qty10,0-stk_qty11,0-stk_qty12,0-stk_qty13, 0-stk_qty14,0-stk_qty15,0-stk_qty16,0-stk_qty17
  from bos_stk
  where stk_mod_id not in (select pandian_mod_id from bos_pandian_det)
  SET IDENTITY_INSERT bos_stk_cy Off

Answer 10

SET IDENTITY_INSERT bos_stk_cy On
insert 表a(a int,b int) select a,b from 表b  
SET IDENTITY_INSERT bos_stk_cy Off

Answer 11

不好意思，刚才可能试错了，现在可以了 谢谢pengdali(大力)