注解:--

之后可以跟上表名填上参数,选中直接执行。(之前对这个不熟悉,记录下来以免忘记)

eg.:

USE [PPP]
GO
/****** Object:  StoredProcedure [dbo].[test_roughGrindingYieldMonthReportLaiLiaoJiaGong]    Script Date: 03/14/2020 09:10:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- m.board_type not in (''9'')
-- test_roughGrindingYieldMonthReportLaiLiaoJiaGong '2020-03-01','2020-03-14','1'
--
ALTER PROCEDURE [dbo].[test_roughGrindingYieldMonthReportLaiLiaoJiaGong]
     @startDate varchar(20),
     @endDate varchar(20),
     @factory varchar(2)
AS
BEGIN

declare @strWhere1 varchar(200)

IF (@factory IS NOT NULL and @factory<>'')
SET @strWhere1 = ' and o.factory= ''' + @factory + ''' '
else
SET @strWhere1 = ' and 1 = 1 '



EXEC ('select m.jumbo_plates_id as jumboId, m.jumbo_plates_type as jumboDesc, m.jumbo_plates_thickness as thickness,

                    isnull(dev9.sqm,0)+isnull(dev99.sqm,0) as sqm9, isnull(dev9.mt,0)+isnull(dev99.mt,0)  as mt9
                    from jumbo_plates_main as m


                   /* 来料加工 */

                    left join(select n.jumbo_id, sum(n.total_sqm) as sqm, sum(n.total_weight)  as mt from
                    (select  o.jumbo_id, o.total_sqm, o.total_weight from online_record_shift as o

                    where o.input_date>=''' + @startDate + ''' ' + @strWhere1 + ' and o.input_date<=''' + @endDate + ''' and SUBSTRING(o.jumbo_id,1,2)=''LL'' and o.last_processing_number in (''20'',''30'',''40'',''50'',''60'')
                    and o.processing_number not in (''20'',''30'',''40'',''50'',''60'')
                     and o.last_processing_number<>o.processing_number) as n group by n.jumbo_id) as dev9
                    on(dev9.jumbo_id=m.jumbo_plates_id)




                    left join(select n.material_number, sum(n.total_sqm) as sqm, sum(n.total_weight)  as mt from
                    (select o.processing_order_number,
                    o.processing_order_item_number,  o.stock_out_quantity, i.material_number, c.sqm_perpiece, i.material_thickness,o.total_sqm, o.total_weight from finished_product_stock_out as o

                    inner join(select processing_order_number, processing_order_item_number, group_id, material_number, sqm_perpiece, material_thickness from processing_order_items where SUBSTRING(material_number,1,2)=''LL'') as i
                    on(i.processing_order_number=o.processing_order_number and i.processing_order_item_number=o.processing_order_item_number)

                    inner join(select processing_order_number, processing_order_item_number, sqm_perpiece from processing_product )as c
                    on(c.processing_order_number=o.processing_order_number and c.processing_order_item_number=o.processing_order_item_number)

                    inner join(select processing_order_number from processing_orders where invoice_area=''2'') as p
                    on(p.processing_order_number=o.processing_order_number)

                    where o.stock_out_date>=''' + @startDate + ''' and o.stock_out_date<=''' + @endDate + ''' and  o.withdraw_flag=''0'' ' + @strWhere1 + ') as n group by n.material_number) as dev99
                    on(dev99.material_number=m.jumbo_plates_id)

                     order by m.jumbo_plates_id')

END
08-30 17:32
查看更多