存储过程里一个莫名其妙的问题

Question

create PROCEDURE usp_balance_sheet_store(@as_code varchar(50),@ld_date_start datetime,@ld_date_end datetime,@out_return decimal(1,0) out)
/*
过程定义说明：
应用场景：库存结存,包括：材料库，半成品库，委外库(过程调用失败后，自动回滚!)
参数说明：@as_code    ：计划周期编号
  @ld_date_start ：起始日期
          @ld_date_end ：截止日期
          @out_return ：过程返回值(-1:失败;1:成功)
创建人：
创建时间：2010年4月26日
修改人：
修改时间：
*/
AS
declare @keymax decimal(16,0);
    declare @ultimo_num decimal(16,0);
    declare @recent_code varchar(50);
BEGIN begin tran
--测试变量
  --set @ld_date_start = '2010-04-21 00:00:00';
  --set @ld_date_end = '2010-04-30 00:00:00';
  
  --查找是否存在最近一次结存的计划周期编号
  select @recent_code = isNull(MAX(mes_material_balance.mes_period_code),'') 
  from mes_material_balance
  where mes_material_balance.mes_period_code < @as_code
  
--第一部分++++++++++++++++++插入材料库库存结存数据++++++++++++++++++++++++INSERT INTO mes_material_balance
(
  --pk_id,
  mes_period_code,
  part_pkid,
  stock_pkid,
  organ_pkid,
  organ_code,
  organ_name,
  ultimo_num,
  ultimo_money,
  in_num,
  in_money,
  out_num,
  out_money ,
  stuff_dross_num,
  stuff_dross_num_money,
  process_dross_num,
  process_dross_num_money,
  balance_num,
  balance_money,
  stock_price,
  kj_year,
  kj_month,
  kj_monthly,
  keeper_id,
  prot_code,
  prot_name,
  prot_sort,
  flag_type
)
(
  select --dbo.f_getkey('mes_material_balance'),
   @as_code
   ,out_ims_part_stock.part_pkid
        ,out_ims_part_stock.stock_pkid
        ,NULL
        ,NULL
        ,NULL
        ,case WHEN LEN(@recent_code) > 0 THEN
(SELECT isNull(sum(mes_material_balance.ultimo_num),0)
FROM mes_material_balance 
WHERE mes_material_balance.mes_period_code = @as_code
AND mes_material_balance.part_pkid = out_ims_part_stock.part_pkid
AND mes_material_balance.stock_pkid = out_ims_part_stock.stock_pkid
)
--            dbo.f_getstore_balance(@recent_code
--             ,out_ims_part_stock.part_pkid
--                                ,out_ims_part_stock.stock_pkid
--                                )
         ELSE
                isNull(out_ims_part_stock.part_num,0)
                +
-- dbo.f_getstore_balance_out(out_ims_part_stock.part_pkid
--               ,out_ims_part_stock.stock_pkid
--   ,'MO'
--   ,@ld_date_start
--   )
                (select isNull(sum(out_make_num),0)
                from ims_part_out
                JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_out.stock
                JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_out.part_pkid
                JOIN dms_stock_io_type on ims_part_out.io_type = dms_stock_io_type.io_code
                where dms_stock_io_type.io_type = 'MO'
                and ims_part_out.out_date >= @ld_date_start
                )
                -
-- dbo.f_getstore_balance_out(out_ims_part_stock.part_pkid
--               ,out_ims_part_stock.stock_pkid
--   ,'MI'
--   ,@ld_date_start
--   )
                (SELECT sum(isNull(free_num,0) + isNull(make_num,0))
                FROM ims_part_in
                JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_in.stock
                JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_in.part_pkid
                JOIN dms_stock_io_type on ims_part_in.in_type = dms_stock_io_type.io_code
                WHERE dms_stock_io_type.io_type = 'MI'
                AND ims_part_in.in_date >= @ld_date_start
                )
            END --??????期初库存
        ,0 --期初金额
        ,(select sum(isNull(free_num,0) + isNull(make_num,0))
        from ims_part_in
        JOIN dbo.out_ims_part_stock on dbo.out_ims_part_stock.stock_pkid = ims_part_in.stock
        JOIN dbo.out_ims_part_stock on dbo.out_ims_part_stock.part_pkid = ims_part_in.part_pkid
        JOIN dms_stock_io_type on ims_part_in.in_type = dms_stock_io_type.io_code
        where dms_stock_io_type.io_type = 'MI'
        and ims_part_in.in_date >= @ld_date_start
        and ims_part_in.in_date <= @ld_date_end
        ) as work_in --??????本期入库
        ,0 --本期金额
        ,(select isNull(sum(out_make_num),0)
        from ims_part_out
        JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_out.stock
        JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_out.part_pkid
        JOIN dms_stock_io_type on ims_part_out.io_type = dms_stock_io_type.io_code
        where dms_stock_io_type.io_type = 'MO'
        and ims_part_out.out_date >= @ld_date_start
        and ims_part_out.out_date <= @ld_date_end
        ) --??????本期出库
        ,0 --
        ,0 --
        ,0 --
        ,0 --
        ,0 --
        ,isNull(out_ims_part_stock.part_num,0)
         +
                  (select isNull(sum(out_make_num),0)
                  from ims_part_out
                  JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_out.stock
                  JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_out.part_pkid
                  JOIN dms_stock_io_type on ims_part_out.io_type = dms_stock_io_type.io_code
                  where dms_stock_io_type.io_type = 'MO'
                  and ims_part_out.out_date >= @ld_date_start
                  )
                -
                  (select sum(isNull(free_num,0) + isNull(make_num,0))
                  from ims_part_in
                  JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_in.stock
                  JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_in.part_pkid
                  JOIN dms_stock_io_type on ims_part_in.in_type = dms_stock_io_type.io_code
                  where dms_stock_io_type.io_type = 'MI'
                  and ims_part_in.in_date >= @ld_date_start
                  ) --??????期初库存 --??????期末库存
         +
            (select sum(isNull(free_num,0) + isNull(make_num,0))
              from ims_part_in
              JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_in.stock
              JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_in.part_pkid
              JOIN dms_stock_io_type on ims_part_in.in_type = dms_stock_io_type.io_code
              where dms_stock_io_type.io_type = 'MI'
              and ims_part_in.in_date >= @ld_date_start
              and ims_part_in.in_date <= @ld_date_end
              ) --??????本期入库
--            -
--            (select isNull(sum(out_make_num),0)
--              from ims_part_out
--              JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_out.stock
--              JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_out.part_pkid
--              JOIN dms_stock_io_type on ims_part_out.io_type = dms_stock_io_type.io_code
--              where dms_stock_io_type.io_type = 'MO'
--              and ims_part_out.out_date >= @ld_date_start
--              and ims_part_out.out_date <= @ld_date_end
--              )
--??????期末结存  
        ,0
        ,0
        ,null --
        ,null --
        ,null --
        ,null --
        ,out_mes_part_info.prot_code --
        ,out_mes_part_info.prot_name --
        ,out_mes_part_info.prot_sort --
        ,3 -
  from dbo.ims_part_stock out_ims_part_stock 
  JOIN dbo.dms_data_stock out_dms_data_stock on out_ims_part_stock.stock_pkid = out_dms_data_stock.pk_id
  JOIN dbo.dms_data_stock_attr out_dms_data_stock_attr on out_dms_data_stock.pk_id = out_dms_data_stock_attr.stock_pkid
  JOIN dbo.tms_part_info out_tms_part_info on out_ims_part_stock.part_pkid = out_tms_part_info.pk_id
  LEFT OUTER JOIN dbo.mes_part_info out_mes_part_info on out_tms_part_info.pk_id = out_mes_part_info.info_pkid
  where out_dms_data_stock_attr.dict_code = '03'
  and out_dms_data_stock.stock_code not in ('24','25','28','29')
 )if @@error <> 0
    BEGIN
      set @out_return = -1;
      rollback TRANSACTION;
    END
--  else
--    BEGIN
      set @out_return = 1;
      commit TRANSACTION;
--    ENDEND
这里是过程代码的一部分，其它部分我给截掉了，保留的部分出现一个奇怪的现象，我用绿色和红色分别标出了。当我保留绿色部分代码，注释红色代码时，编译通过，但执行提示【消息 208，级别 16，状态 1，过程 usp_balance_sheet_store，第 34 行
对象名  'out_ims_part_stock' 无效。
消息 266，级别 16，状态 2，过程 usp_balance_sheet_store，第 34 行
EXECUTE 后的事务计数指示缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。上一计数 = 4，当前计数 = 5。】，做相反操作，注释绿色代码，保留红色代码时，编译，运行都能通过，但查询慢的离谱。之所以建一个函数来做相同的查询只是一个不得已的法子，目的就是使运行时不报上述出现的无效对象的问题，但这个真不是我希望要的结果，很无奈。后面的代码，本期入库、本期出库、期末结存的地方按现有的编写方式，执行时全都会出现上述的错误，难不成要我把所有子查询都改成函数调用不成？ 盼望有识之士提点一二，不吝感激！

Answer 1

[color=#FF0000] 标记处是红色的代码部分
[color=#339966] 标记处是绿色的代码部分帖子发布后，不知为什么没有体现出来，特此告知大家。

Answer 2

(SELECT isNull(sum(mes_material_balance.ultimo_num),0)
                FROM mes_material_balance 
                WHERE mes_material_balance.mes_period_code = @as_code
                AND mes_material_balance.part_pkid = out_ims_part_stock.part_pkid
                AND mes_material_balance.stock_pkid = out_ims_part_stock.stock_pkid
            )
这里面都没有from 怎么会有效呢

Answer 3

怎么没有from ？
第二行 第一个不是？

Answer 4

两个out_ims_part_stock 表关连要用别名

Answer 5

from ims_part_out
                JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_out.stock
                JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_out.part_pkid
                JOIN dms_stock_io_type on ims_part_out.io_type = dms_stock_io_type.io_code

Answer 6

哇...太厉害了你！我看了好久才看出来这是一条select语句！不过真的很长，真的看不出错误！呵呵...

Answer 7

这里不是想连2个表，而是连一个表，不过是两个条件。
from ims_part_out
,out_ims_part_stock
where out_ims_part_stock.stock_pkid = ims_part_out.stock
and out_ims_part_stock.part_pkid = ims_part_out.part_pkid
我想这么写，可能会让您明白。

Answer 8

你写出来的就是4个表相内联，其中有两个out_ims_part_stock一模一样 

Answer 9

如果是两个条件，那就不能用join了只能用
from ims_part_out
  JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_out.stock
  and  out_ims_part_stock.part_pkid = ims_part_out.part_pkid
  JOIN dms_stock_io_type on ims_part_out.io_type = dms_stock_io_type.io_code

Answer 10

-- 你的写法有问题，两个表关联应该用and而不是Join 两次。
JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_out.stock
and out_ims_part_stock.part_pkid = ims_part_out.part_pk……

Answer 11

写长SQL语句根本没必要，个人建议还是一步步慢慢来吧，多表关联效率也不高

Answer 12

 from ims_part_out
        JOIN out_ims_part_stock on out_ims_part_stock.stock_pkid = ims_part_out.stock
        JOIN out_ims_part_stock on out_ims_part_stock.part_pkid = ims_part_out.part_pkid
估计是两个out_ims_part_stock 未用别名,加上别名试试,对case内的查询,sql server可能无法分出是引用那个表的字段.

Answer 13

经过反复的验证，chuifengde 您指出了我的一处错误，相当准确，我没有意识到，非常感激。但那并不是这个过程中唯一的问题。呵呵，现在这个过程已经被我修改通过。
最后，把问题总结下。
1>在批量插入数据时，我在最外层from dbo.ims_part_stock out_ims_part_stock 
  JOIN dbo.dms_data_stock out_dms_data_stock on out_ims_part_stock.stock_pkid = out_dms_data_stock.pk_id
  JOIN dbo.dms_data_stock_attr out_dms_data_stock_attr on out_dms_data_stock.pk_id = out_dms_data_stock_attr.stock_pkid
  JOIN dbo.tms_part_info out_tms_part_info on out_ims_part_stock.part_pkid = out_tms_part_info.pk_id
  LEFT OUTER JOIN dbo.mes_part_info out_mes_part_info on out_tms_part_info.pk_id = out_mes_part_info.info_pkid
  where out_dms_data_stock_attr.dict_code = '03'
  and out_dms_data_stock.stock_code not in ('24','25','28','29')这部分代码中，我对dbo.ims_part_stock使用了别名 out_ims_part_stock ，在内部子查询时，引用外部表的别名时，产生了对象无效的问题。我把别名去掉，直接引用外部表，就没有对象无效的问题了。由此，我认为：在写sqlserver的子查询时，外部表尽可能不创建别名，在子查询中直接引用外部表名，如果遇到与子查询表名相重复时，将内部表进行别名命名，这样就不会有指代模糊的问题了。
2>过去习惯用
from ims_part_out
,out_ims_part_stock
where out_ims_part_stock.stock_pkid = ims_part_out.stock
and out_ims_part_stock.part_pkid = ims_part_out.part_pkid
这种方式书写，以至于转换为join方式的写法时，出现如：chuifengde 所指出的问题，对于您的指正非常感激。
3>对于cxmcxm指出的
【对case内的查询,sql server可能无法分出是引用那个表的字段.】这个疑问，我也进行了验证，结论是在case中使用子查询也是可以行的通的。最后，感谢大家的帮助，特别感谢【chuifengde】 的指正和【cxmcxm】的提点，谢谢。