[求助]怎樣實現參數的級連轉遞？

HG

現在把傳遞參數得到的數據用返回表函數來實現，然後再對函數進得操作，向成功又走近了一步
--use production
/*
create function ufn_order (@my_order as varchar(10))
returns table   as
return  ( select * from [order] where order_no = @my_order)
*/
--select * from ufn_order ('dr9999')
/*
create function ufn_order_item (@my_order as varchar(10))
returns table as
return (select * from order_item where order_no = @my_order)
*/
--select  * from ufn_order_item('dr9999')
/*
create function ufn_ko (@my_order as varchar(10))
returns table as
return (select * from ko where order_no = @my_order)
*/
--select * from ufn_ko('dr9999')
/*
create function ufn_dye (@my_order as varchar(10))
returns table as
return (select * from dye where ko_no in (select ko_no from ko where order_no = @my_order))
*/
--select * from ufn_dye('dr9999')
/*
create function ufn_templet (@my_order as varchar(10))
returns table as
return (select * from templet where dye_no in (select dye_no from dye where ko_no in (select ko_no from ko where order_no in (select order_no = @my_order))))
*/
--select * from ufn_templet ('dr9999')
/*
create function ufn_tub (@my_order as varchar(10))
returns table as
return (select * from tub where tmp_no in (select tmp_no from templet where dye_no in (select dye_no from dye where ko_no in (select ko_no from ko where order_no in (select order_no = @my_order)))))
*/
---select * from ufn_tub('dr9999')
心得,對於復雜的情況，應一步一步來處理，積跬步致千裡。個個擊破，不要貪多。

HG

(附代碼)最終效果的實現
alter procedure usp_qry_production @my_jobno varchar(10)   
as   
select ufn_order.order_no,ufn_order.cust_id,ufn_order.cust_po,
          ufn_order_item.order_item_no,ufn_order_item.order_item_mercery,ufn_order_item.order_item_require,ufn_order_item.order_item_require,ufn_order_item.order_item_begin_date,ufn_order_item.order_item_end_date,   
         ufn_ko.ko_no,ufn_ko.order_no,ufn_ko.order_item_no,ufn_ko.ko_begin_date,ufn_ko.ko_end_date,
         ufn_dye.dye_no,ufn_dye.dye_begin_date,ufn_dye.dye_end_date,
          ufn_templet.tmp_no,ufn_templet.tmp_cust_color_no,ufn_templet.tmp_begin_date,ufn_templet.tmp_end_date,ufn_templet.tmp_pass,
          ufn_tub.tub_no,ufn_tub.tub_pt,ufn_tub.tub_begin_datetime,ufn_tub.tub_end_datetime,
           ufn_midjerque.mj_open,ufn_midjerque.mj_ok,ufn_midjerque.mj_ok_date,
           ufn_trim.trim_before,ufn_trim.trim_after,ufn_trim.trim_ok,ufn_trim.trim_ok_date,
           ufn_qa.qa_ok,ufn_qa.qa_ok_date,
           ufn_delivery.delivery_no,ufn_delivery.delivery_bar,ufn_delivery.delivery_pt,ufn_delivery.delivery_date
  from  ufn_order(@my_jobno)
         left outer join ufn_order_item(@my_jobno) on ufn_order.order_no = ufn_order_item.order_no
           left outer join ufn_ko(@my_jobno) on ufn_order_item.order_no = ufn_ko.order_no and ufn_order_item.order_item_no = ufn_ko.order_item_no
             left outer join  ufn_dye(@my_jobno)  on ufn_ko.ko_no = ufn_dye.ko_no
               left outer join ufn_templet(@my_jobno) on ufn_dye.dye_no = ufn_templet.dye_no
                  left outer join ufn_tub(@my_jobno) on  ufn_templet.tmp_no =ufn_tub.tmp_no
                    left outer join ufn_midjerque(@my_jobno) on ufn_tub.tub_no = ufn_midjerque.tub_no
                      left outer join ufn_trim(@my_jobno) on ufn_midjerque.tub_no = ufn_trim.tub_no
                        left outer join ufn_qa(@my_jobno) on ufn_trim.tub_no = ufn_qa.tub_no
                          left outer join ufn_delivery(@my_jobno) on ufn_qa.tub_no = ufn_delivery.tub_no

HG

夠不夠復雜，是不是把SELECT語句和生成表函數功能超常發揮了。

WTM1

高级，看都看晕了！
回家研究！