存储过程返回表数据 

 1 ALTER PROC [dbo].[DBUnfinishedJobrouteSP]
 2 (
 3     @Wc WcType,--工作中心
 4     @Job JobType=null --作业单
 5 )
 6 AS
 7 BEGIN 
 8     
 9     --结果返回表
10     DECLARE @DBUnfinishedJobroute TABLE(
11     co_num CoNumType NULL--客户订单号
12    ,Job JobType NULL--作业单号
13    ,cust_num CustNumType NULL--客户号
14    ,cust_name    NameType NULL--客户名称
15    ,item    ItemType NULL--物料
16    ,description    DescriptionType NULL--物料说明
17    ,qty_released    DescriptionType NULL--下达数量
18    ,Item_Desc    NVARCHAR(80) NULL--规格
19    ,oper_num    OperNumType  NULL--工序
20    ,wc    WcType  NULL--工序代码
21    ,Oper_Code    JobType NULL--工作代码
22    ,Selected    ApsIntType NULL--选择
23     )
24     
25     INSERT INTO @DBUnfinishedJobroute
26             ( co_num ,
27               Job ,
28               cust_num ,
29               cust_name ,
30               item ,
31               description ,
32               qty_released ,
33               Item_Desc ,
34               oper_num ,
35               wc ,
36               Oper_Code ,
37               Selected
38             )
39     SELECT 
40         c.ord_num,
41         a.job,
42         c.cust_num,
43         '' AS cust_name,
44         c.item,
45         c.description,
46         c.qty_released,
47         '' AS Item_Desc,
48         a.oper_num,
49         a.wc,
50         a.Uf_JobRoute_OperCode,
51         0 AS Selected
52         FROM dbo.jobroute a
53         inner JOIN jrt_sch b ON a.job=b.job AND a.suffix=b.suffix AND a.oper_num=b.oper_num
54         LEFT JOIN job c ON a.job=c.job AND a.suffix=c.suffix
55         WHERE 
56         a.wc=@WC 
57         AND (ISNULL(@Job,'')='' OR c.Job=@Job)
58 
59 
60     SELECT * FROM @DBUnfinishedJobroute

 

posted @ 2017-09-27 12:32  温故余学  阅读(2175)  评论(0)  编辑  收藏  举报